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

FROM CWP_14865_1045_1421068032
GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS

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