RE: missing part of sql statement in v$sql

The suspicion arises that somehow you have unprintable characters in the original source string or some such and corresponding rubout characters.  

If possible, view your source via some editor that shows a visible encoding of unprintables, or failing that carefully manipulate your input sql string.  

When (if) all that fails, I would resort to inserting

--< comment line>

between each source text line of the text, the entire thing by hand, if need be.  

Something very fishy is afoot.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ken Naim Sent: Saturday, September 10, 2016 12:21 AM To: 'Beckstrom Jeffrey'; christopherdtaylor1994_at_gmail.com Cc: 'oracle-l-freelists'; rjanuary_at_gmail.com Subject: RE: missing part of sql statement in v$sql  

Same, as v$sql.  

From: Beckstrom Jeffrey [mailto:JBECKSTROM_at_gcrta.org] Sent: Friday, September 9, 2016 3:30 PM
To: christopherdtaylor1994_at_gmail.com; kennethnaim_at_gmail.com Cc: oracle-l-freelists <oracle-l_at_freelists.org>; rjanuary_at_gmail.com Subject: RE: missing part of sql statement in v$sql  

What about v$sqltext?  

Jeffrey Beckstrom
Lead Database Administrator

Information Technology Department

Greater Cleveland Regional Transit Authority

1240 W. 6th Street
Cleveland, Ohio 44113  

>>> "Ken Naim" <kennethnaim_at_gmail.com> 9/9/16 3:29 PM >>>

Thank you, I searched on metalink and couldn’t find the right keywords for proper search results. I was hoping someone ran into it before.  

From: Chris Taylor [mailto:christopherdtaylor1994_at_gmail.com] Sent: Friday, September 9, 2016 3:01 PM
To: kennethnaim_at_gmail.com
Cc: Ryan January <rjanuary_at_gmail.com>; Oracle-l Digest Users <oracle-l_at_freelists.org> Subject: Re: missing part of sql statement 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)  

V$SQL.SQL_FULLTEXT For Create Table Statement Is Trimmed (Doc ID 1922923.1)  

On Fri, Sep 9, 2016 at 1:49 PM, Ken Naim <kennethnaim_at_gmail.com> wrote:

Sql_fulltext is missing the 10-20 lines, sql_text has only the first 20 or so characters. It’s so odd that the middle of a statement would be missing.  

Thanks,

Ken  

From: Ryan January [mailto:rjanuary_at_gmail.com] Sent: Friday, September 9, 2016 2:42 PM
To: kennethnaim_at_gmail.com
Cc: Oracle-l Digest Users <oracle-l_at_freelists.org> Subject: Re: missing part of sql statement in v$sql  

What about sql_fulltext? Is it complete, or also truncated?  

On Sep 9, 2016, at 1:39 PM, Ken Naim <kennethnaim_at_gmail.com> wrote:  

I’ve queried v$sql for a particular poorly performing sql_id and I get the first and last part but it is missing maybe 10-20 lines from just after the first create table statement. Sql_text has just “CREATE TABLE CWC_148D1”. DB version is 11.2.0.4.  

CREATE TABLE CWC_148D1 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_BASE1) AS SEC_BASE1

,MAX(IS_MODIFIED0) AS IS_MODIFIED0

,MAX(IS_MODIFIED1) AS IS_MODIFIED1
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))) = -1) THEN 1 END) AS NEED_MERGE
FROM CWP_14865_1045_1421068032 GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS
,(SELECT LEVEL_ID1,LEVEL_ID3,COUNT(*) AS WEIGHT_BASE0
FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3 FROM CWP_14865_1045_1421068032 WHERE IS_BASE0 = 1)WEIGHT_BASE0 GROUP BY LEVEL_ID1,LEVEL_ID3)WEIGHT_BASE0
,(SELECT LEVEL_ID1,LEVEL_ID3,LEVEL_ID2,COUNT(*) AS WEIGHT_BASE1
FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_ID1,LEVEL_ID3,LEVEL_ID2 FROM CWP_14865_1045_1421068032 WHERE IS_BASE1 = 1)WEIGHT_BASE1 GROUP BY LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1 WHERE BASE0_COMBS.LEVEL_ID1 = ALL_COMBS.LEVEL_ID1 AND BASE0_COMBS.LEVEL_ID3 = ALL_COMBS.LEVEL_ID3 AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE0.LEVEL_ID1(+) AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE0.LEVEL_ID3(+) AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE1.LEVEL_ID1(+) AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE1.LEVEL_ID3(+) AND ALL_COMBS.LEVEL_ID2 = WEIGHT_BASE1.LEVEL_ID2(+) AND (BASE0_COMBS.NEED_MERGE IS NULL OR (BASE0_COMBS.NEED_MERGE = ALL_COMBS.IS_BASE1)))T_POPU_LIST
,T_EP_EBS_ACCOUNT

,PROMOTION

,T_EP_FISCAL_MONTH
WHERE T_POPU_LIST.LEVEL_ID1 = T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID AND T_POPU_LIST.LEVEL_ID2 = PROMOTION.PROMOTION_ID AND T_POPU_LIST.LEVEL_ID3 = T_EP_FISCAL_MONTH.T_EP_FISCAL_MONTH_ID AND T_POPU_LIST.IS_BASE1 = 1         .

--
http://www.freelists.org/webpage/oracle-l