RE: missing part of sql statement in v$sql

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 <mailto: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