Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 2B2371960731 for ; Fri, 9 Sep 2016 21:37:29 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Fri, 9 Sep 2016 21:37:29 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DFA92C9DB; Fri, 9 Sep 2016 15:37:28 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id uyyU2HTrZBuv; Fri, 9 Sep 2016 15:37:27 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1CE8A2CBF4; Fri, 9 Sep 2016 15:37:15 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 09 Sep 2016 15:35:53 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 63DCF2CB35 for ; Fri, 9 Sep 2016 15:35:53 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id BVEL_FNy9KfR for ; Fri, 9 Sep 2016 15:35:53 -0400 (EDT) Received: from nbfkord-smmo03.seg.att.com (nbfkord-smmo03.seg.att.com [209.65.160.84]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 5AF052CB34 for ; Fri, 9 Sep 2016 15:35:52 -0400 (EDT) Received: from unknown [70.238.103.170] (EHLO groupwise2014.gcrta.org) by nbfkord-smmo03.seg.att.com(mxl_mta-7.2.4-7) over TLS secured channel with ESMTP id 71f03d75.0.2257374.00-2279.5175416.nbfkord-smmo03.seg.att.com (envelope-from ); Fri, 09 Sep 2016 19:35:53 +0000 (UTC) X-MXL-Hash: 57d30f1942deab5c-8411fa040920aed21873496ba202c833be675efa Received: from Main1-MTA by groupwise2014.gcrta.org with Novell_GroupWise; Fri, 09 Sep 2016 15:31:37 -0400 Message-Id: <57D2D5780200000B000177E6@groupwise2014.gcrta.org> Date: Fri, 09 Sep 2016 15:30:00 -0400 From: "Beckstrom Jeffrey" To: , Cc: "oracle-l-freelists" , Subject: RE: missing part of sql statement in v$sql References: <014001d20ac9$7de464f0$79ad2ed0$@gmail.com> <014701d20aca$f085ae50$d1910af0$@gmail.com> <016301d20ad0$837abca0$8a7035e0$@gmail.com> In-Reply-To: <016301d20ad0$837abca0$8a7035e0$@gmail.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=_reb-r50794080-t57D30F19" X-Processed-By: Rebuild v2.0-0 X-AnalysisOut: [v=2.1 cv=Xs/8TAx9 c=1 sm=1 tr=0 a=Lw95f4ehmtAMSMquLqXxhg==] X-AnalysisOut: [:17 a=GW1xBdLrtEIA:10 a=ypafHwEPi9q-7uupzQAA:9 a=pGLkceISA] X-AnalysisOut: [AAA:8 a=D_VmOLqjAAAA:8 a=r23b1ccxc2Z6WisXCZUA:9 a=QEXdDO2u] X-AnalysisOut: [t3YA:10 a=RCPcuHpertqrIQjPXGwA:9 a=a19fGRdVjEfpgTf0:21 a=_] X-AnalysisOut: [W_S_7VecoQA:10 a=6kGIvZw6iX1k4Y-7sg4_:22 a=d_xflH664_jzgZD] X-AnalysisOut: [B7q-c:22] X-Spam: [F=0.2000000000; CM=0.500; S=0.200(2015072901)] X-MAIL-FROM: X-SOURCE-IP: [70.238.103.170] X-archive-position: 66189 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: JBECKSTROM@gcrta.org Precedence: normal Reply-To: JBECKSTROM@gcrta.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --=_reb-r50794080-t57D30F19 Content-Type: multipart/alternative; boundary="=_reb-r11162BF8-t57D30F19" --=_reb-r11162BF8-t57D30F19 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: base64 V2hhdCBhYm91dCB2JHNxbHRleHQ/DQoNCkplZmZyZXkgQmVja3N0cm9tDQpMZWFkIERhdGFi YXNlIEFkbWluaXN0cmF0b3INCkluZm9ybWF0aW9uIFRlY2hub2xvZ3kgRGVwYXJ0bWVudA0K R3JlYXRlciBDbGV2ZWxhbmQgUmVnaW9uYWwgVHJhbnNpdCBBdXRob3JpdHkNCjEyNDAgVy4g NnRoIFN0cmVldA0KQ2xldmVsYW5kLCBPaGlvIDQ0MTEzDQoNCiA+Pj4gIktlbiBOYWltIiA8 a2VubmV0aG5haW1AZ21haWwuY29tPiA5LzkvMTYgMzoyOSBQTSA+Pj4NClRoYW5rIHlvdSwg SSBzZWFyY2hlZCBvbiBtZXRhbGluayBhbmQgY291bGRu4oCZdCBmaW5kIHRoZSByaWdodCBr ZXl3b3Jkcw0KZm9yIHByb3BlciBzZWFyY2ggcmVzdWx0cy4gSSB3YXMgaG9waW5nIHNvbWVv bmUgcmFuIGludG8gaXQgYmVmb3JlLg0KIA0KRnJvbTogQ2hyaXMgVGF5bG9yIFttYWlsdG86 Y2hyaXN0b3BoZXJkdGF5bG9yMTk5NEBnbWFpbC5jb21dIA0KU2VudDogRnJpZGF5LCBTZXB0 ZW1iZXIgOSwgMjAxNiAzOjAxIFBNDQpUbzoga2VubmV0aG5haW1AZ21haWwuY29tDQpDYzog UnlhbiBKYW51YXJ5IDxyamFudWFyeUBnbWFpbC5jb20+OyBPcmFjbGUtbCBEaWdlc3QgVXNl cnMNCjxvcmFjbGUtbEBmcmVlbGlzdHMub3JnPg0KU3ViamVjdDogUmU6IG1pc3NpbmcgcGFy dCBvZiBzcWwgc3RhdGVtZW50IGluIHYkc3FsDQogDQpUSGVyZSdzIGEgYnVnIGluIDExLjIu MC40IHdpdGggbWlzc2luZyBTUUxfVEVYVCAtIHlvdSBtYXkgYmUgaGl0dGluZyBpdA0KKGFu ZCBhcHBhcmVudGx5IHNldmVyYWwgcmVsYXRlZCBidWdzKQ0KIA0KViRTUUwuU1FMX0ZVTExU RVhUIEZvciBDcmVhdGUgVGFibGUgU3RhdGVtZW50IElzIFRyaW1tZWQgKERvYyBJRA0KMTky MjkyMy4xKQ0KIA0KT24gRnJpLCBTZXAgOSwgMjAxNiBhdCAxOjQ5IFBNLCBLZW4gTmFpbSA8 a2VubmV0aG5haW1AZ21haWwuY29tPg0Kd3JvdGU6DQpTcWxfZnVsbHRleHQgaXMgbWlzc2lu ZyB0aGUgMTAtMjAgbGluZXMsIHNxbF90ZXh0IGhhcyBvbmx5IHRoZSBmaXJzdCAyMA0Kb3Ig c28gY2hhcmFjdGVycy4gSXTigJlzIHNvIG9kZCB0aGF0IHRoZSBtaWRkbGUgb2YgYSBzdGF0 ZW1lbnQgd291bGQgYmUNCm1pc3NpbmcuDQogDQpUaGFua3MsDQpLZW4NCiANCkZyb206IFJ5 YW4gSmFudWFyeSBbbWFpbHRvOnJqYW51YXJ5QGdtYWlsLmNvbV0gDQpTZW50OiBGcmlkYXks IFNlcHRlbWJlciA5LCAyMDE2IDI6NDIgUE0NClRvOiBrZW5uZXRobmFpbUBnbWFpbC5jb20N CkNjOiBPcmFjbGUtbCBEaWdlc3QgVXNlcnMgPG9yYWNsZS1sQGZyZWVsaXN0cy5vcmc+DQpT dWJqZWN0OiBSZTogbWlzc2luZyBwYXJ0IG9mIHNxbCBzdGF0ZW1lbnQgaW4gdiRzcWwNCiAN CldoYXQgYWJvdXQgc3FsX2Z1bGx0ZXh0PyAgSXMgaXQgY29tcGxldGUsIG9yIGFsc28gdHJ1 bmNhdGVkPw0KIA0KT24gU2VwIDksIDIwMTYsIGF0IDE6MzkgUE0sIEtlbiBOYWltIDxrZW5u ZXRobmFpbUBnbWFpbC5jb20+IHdyb3RlOg0KIA0KSeKAmXZlIHF1ZXJpZWQgdiRzcWwgZm9y IGEgcGFydGljdWxhciBwb29ybHkgcGVyZm9ybWluZyBzcWxfaWQgYW5kIEkgZ2V0DQp0aGUg Zmlyc3QgYW5kIGxhc3QgcGFydCBidXQgaXQgaXMgbWlzc2luZyBtYXliZSAxMC0yMCBsaW5l cyBmcm9tIGp1c3QNCmFmdGVyIHRoZSBmaXJzdCBjcmVhdGUgdGFibGUgc3RhdGVtZW50LiBT cWxfdGV4dCBoYXMganVzdCDigJxDUkVBVEUgVEFCTEUNCkNXQ18xNDhEMeKAnS4gREIgdmVy c2lvbiBpcyAxMS4yLjAuNC4NCiANCkNSRUFURSBUQUJMRSBDV0NfMTQ4RDEgQVMgSVNfTU9E SUZJRUQxDQpGUk9NIChTRUxFQ1QgTEVWRUxfSUQxLExFVkVMX0lEMixMRVZFTF9JRDMNCixN QVgoSVNfQkFTRTApIEFTIElTX0JBU0UwDQosTUFYKElTX0JBU0UxKSBBUyBJU19CQVNFMQ0K LE1JTihTRUNfQkFTRTApIEFTIFNFQ19CQVNFMA0KLE1JTihTRUNfQkFTRTEpIEFTIFNFQ19C QVNFMQ0KLE1BWChJU19NT0RJRklFRDApIEFTIElTX01PRElGSUVEMA0KLE1BWChJU19NT0RJ RklFRDEpIEFTIElTX01PRElGSUVEMQ0KRlJPTSBDV1BfMTQ4NjVfMTA0NV8xNDIxMDY4MDMy DQpHUk9VUCBCWSBMRVZFTF9JRDEsTEVWRUxfSUQyLExFVkVMX0lEMylBTExfQ09NQlMNCiwo U0VMRUNUIExFVkVMX0lEMSxMRVZFTF9JRDMNCiwoQ0FTRSBXSEVOICgoTUlOKE5WTChJU19C QVNFMSwtMSkpKk1BWChOVkwoSVNfQkFTRTEsLTEpKSkgPSAtMSkgVEhFTiAxDQpFTkQpIEFT IE5FRURfTUVSR0UNCkZST00gQ1dQXzE0ODY1XzEwNDVfMTQyMTA2ODAzMg0KR1JPVVAgQlkg TEVWRUxfSUQxLExFVkVMX0lEMylCQVNFMF9DT01CUw0KLChTRUxFQ1QgTEVWRUxfSUQxLExF VkVMX0lEMyxDT1VOVCgqKSBBUyBXRUlHSFRfQkFTRTANCkZST00gKFNFTEVDVCBESVNUSU5D VCBJVEVNX0lELExPQ0FUSU9OX0lELExFVkVMX0lEMSxMRVZFTF9JRDMNCkZST00gQ1dQXzE0 ODY1XzEwNDVfMTQyMTA2ODAzMg0KV0hFUkUgSVNfQkFTRTAgPSAxKVdFSUdIVF9CQVNFMA0K R1JPVVAgQlkgTEVWRUxfSUQxLExFVkVMX0lEMylXRUlHSFRfQkFTRTANCiwoU0VMRUNUIExF VkVMX0lEMSxMRVZFTF9JRDMsTEVWRUxfSUQyLENPVU5UKCopIEFTIFdFSUdIVF9CQVNFMQ0K RlJPTSAoU0VMRUNUIERJU1RJTkNUDQpJVEVNX0lELExPQ0FUSU9OX0lELFBST01PVElPTl9J RCxMRVZFTF9JRDEsTEVWRUxfSUQzLExFVkVMX0lEMg0KRlJPTSBDV1BfMTQ4NjVfMTA0NV8x NDIxMDY4MDMyDQpXSEVSRSBJU19CQVNFMSA9IDEpV0VJR0hUX0JBU0UxDQpHUk9VUCBCWSBM RVZFTF9JRDEsTEVWRUxfSUQzLExFVkVMX0lEMilXRUlHSFRfQkFTRTENCldIRVJFIEJBU0Uw X0NPTUJTLkxFVkVMX0lEMSA9IEFMTF9DT01CUy5MRVZFTF9JRDENCkFORCBCQVNFMF9DT01C Uy5MRVZFTF9JRDMgPSBBTExfQ09NQlMuTEVWRUxfSUQzDQpBTkQgQUxMX0NPTUJTLkxFVkVM X0lEMSA9IFdFSUdIVF9CQVNFMC5MRVZFTF9JRDEoKykNCkFORCBBTExfQ09NQlMuTEVWRUxf SUQzID0gV0VJR0hUX0JBU0UwLkxFVkVMX0lEMygrKQ0KQU5EIEFMTF9DT01CUy5MRVZFTF9J RDEgPSBXRUlHSFRfQkFTRTEuTEVWRUxfSUQxKCspDQpBTkQgQUxMX0NPTUJTLkxFVkVMX0lE MyA9IFdFSUdIVF9CQVNFMS5MRVZFTF9JRDMoKykNCkFORCBBTExfQ09NQlMuTEVWRUxfSUQy ID0gV0VJR0hUX0JBU0UxLkxFVkVMX0lEMigrKQ0KQU5EIChCQVNFMF9DT01CUy5ORUVEX01F UkdFIElTIE5VTEwNCk9SIChCQVNFMF9DT01CUy5ORUVEX01FUkdFID0gQUxMX0NPTUJTLklT X0JBU0UxKSkpVF9QT1BVX0xJU1QNCixUX0VQX0VCU19BQ0NPVU5UDQosUFJPTU9USU9ODQos VF9FUF9GSVNDQUxfTU9OVEgNCldIRVJFIFRfUE9QVV9MSVNULkxFVkVMX0lEMSA9IFRfRVBf RUJTX0FDQ09VTlQuVF9FUF9FQlNfQUNDT1VOVF9FUF9JRA0KQU5EIFRfUE9QVV9MSVNULkxF VkVMX0lEMiA9IFBST01PVElPTi5QUk9NT1RJT05fSUQNCkFORCBUX1BPUFVfTElTVC5MRVZF TF9JRDMgPSBUX0VQX0ZJU0NBTF9NT05USC5UX0VQX0ZJU0NBTF9NT05USF9JRA0KQU5EIFRf UE9QVV9MSVNULklTX0JBU0UxID0gMQ0KIA0KIA0KDQoNCg0KLg== --=_reb-r11162BF8-t57D30F19 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Description: HTML =0A= =0A= =0A= =0A=
W= hat about v$sqltext?

Jeffrey Beckstrom
Lead Database Admin= istrator
=0A=
Information Technology Department
=0A=
Greater Cleveland Regional Transit Authority
=0A=
1240 W. 6th Street
Cleveland, Ohio 44113
=  
>>> "Ken Naim" <= ;kennethnaim@gmail.com> 9/9/16 3:29 PM >>>
= Thank you, I searched on metalink and couldn=E2=80=99t find the right keywo= rds for proper search results. I was hoping someone ran into it before.
 = ;
From: Chris Taylor [mailto:christopherdtaylor1994@gmail.com]
Sent: = Friday, September 9, 2016 3:01 PM
To: kennethnaim@gmail.com
Cc: Ryan January <rjanuary@gmail.com>; Oracle-l Digest Users <= ;oracle-l@freelists.org>
Subject: Re: missing part of sql stat= ement in v$sql
 
THere's a bug in 11.2.0.4 with missing SQL_TEXT - you may= be hitting it (and apparently several related bugs)
=
 <= /div>

V$SQL.SQL_FULLTEXT For Create Table Statement Is Trimme= d (Doc ID 1922923.1)

 
On Fri, Sep 9, 2016 at 1:49 PM, Ken Naim <<= a href=3D"mailto:kennethnaim@gmail.com">kennethnaim@gmail.com> wrote= :
Sql_fulltext is missing the 10-20 lines, sql_text has only= the first 20 or so characters. It=E2=80=99s so odd that the middle of a st= atement would be missing.
 
Thanks,
Ken
 
From: Ryan January [mailto:rjanuary@gmail.com]
Sent: Friday, = September 9, 2016 2:42 PM
To: kennethnaim@gmail.com
Cc: Oracle-l Digest Users <oracle-l@freelists.org>
Subject: Re: missing part of sql statement in v$sql
 
What about sq= l_fulltext?  Is it complete, or also truncated?
<= div class=3D"MsoNormal" style=3D"margin: 1em 0em;"> 
<= div>
On Sep 9, 2016, at 1:39 PM, Ken Naim <kennethnaim@gmail.com> wrote:
 
<= div>
I=E2=80=99ve querie= d v$sql for a particular poorly performing sql_id and I get the first and l= ast part but it is missing maybe 10-20 lines from just after the first crea= te table statement. Sql_text has just =E2=80=9CCREATE TABLE CWC_148D1=E2=80= =9D. DB version is 11.2.0.4.
 
CREATE TABLE CWC_148D= 1 AS IS_MODIFIED1
FROM (SELECT LEVEL_ID1,LEVEL_ID2,= LEVEL_ID3
,MAX(IS_BASE0) AS IS_BASE0
,MAX(IS_BASE1) = AS IS_BASE1
,MIN(SEC_BASE0) AS SEC_BASE0
,MIN(SEC_B= ASE1) AS SEC_BASE1
,MAX(IS_MODIFIED0) AS IS_MODIFIE= D0
,MAX(IS_MODIFIED1) AS IS_MODIFIED1
<= div>
FROM CWP_14865= _1045_1421068032
GROUP BY LEVEL_ID1,LEVEL_ID2,LEVEL_ID3)ALL_= COMBS
,(SELECT LEVEL_ID1,LEVEL_ID3
,(CASE WHEN ((MIN= (NVL(IS_BASE1,-1))*MAX(NVL(IS_BASE1,-1))) =3D -1) THEN = 1 END) AS NEED_MERGE
FROM CWP_14865_1045_14210680= 32
GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS
<= /div>
,(SELECT = ;LEVEL_ID1,LEVEL_ID3,COUNT(*) AS WEIGHT_BASE0
FROM (SELE= CT DISTINCT ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3
FROM = ;CWP_14865_1045_1421068032
WHERE IS_BASE0 =3D 1)WEIGHT_B= ASE0
GROUP BY LEVEL_ID1,LEVEL_ID3)WEIGHT_BASE0
,(SELECT&n= bsp;LEVEL_ID1,LEVEL_ID3,LEVEL_ID2,COUNT(*) AS WEIGHT_BASE1
FR= OM (SELECT DISTINCT ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_I= D1,LEVEL_ID3,LEVEL_ID2
FROM CWP_14865_1045_1421068032
WHERE&nb= sp;IS_BASE1 =3D 1)WEIGHT_BASE1
GROUP BY LEVEL_ID1,L= EVEL_ID3,LEVEL_ID2)WEIGHT_BASE1
WHERE BASE0_COMBS.LEVEL_ID1 = =3D ALL_COMBS.LEVEL_ID1
AND BASE0_COMBS.LEVEL_ID3 =3D&nb= sp;ALL_COMBS.LEVEL_ID3
AND ALL_COMBS.LEVEL_ID1 =3D WEIGH= T_BASE0.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 =3D WEIGHT_= BASE0.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID1 =3D WEIGHT_BA= SE1.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 =3D WEIGHT_BAS= E1.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID2 =3D WEIGHT_BAS= E1.LEVEL_ID2(+)
AND (BASE0_COMBS.NEED_MERGE IS NULL
,T_EP_EBS_ACCOUNT
,PROMOTION
,T_EP_FISCAL_MONTH
WHERE=  T_POPU_LIST.LEVEL_ID1 =3D T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT= _EP_ID
AND T_POPU_LIST.LEVEL_ID2 =3D PROMOTION.PROMOTION= _ID
AND T_POPU_LIST.LEVEL_ID3 =3D T_EP_FISCAL_MONTH.T_EP= _FISCAL_MONTH_ID
AND T_POPU_LIST.IS_BASE1 =3D 1
 
 

.
--=_reb-r11162BF8-t57D30F19-- --=_reb-r50794080-t57D30F19-- -- http://www.freelists.org/webpage/oracle-l