Re: missing part of sql statement in v$sql

It seems to get lost throughout the system from when I was seeing it.

Also be aware its with 11.2.0.4 *PLUS *a specific quarterly patch.

Chris

On Fri, Sep 9, 2016 at 2:30 PM, Beckstrom Jeffrey <JBECKSTROM_at_gcrta.org> wrote:

> 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