Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate rows displayed for a column - need to wrap (10g & AIX 6.1)
Duplicate rows displayed for a column - need to wrap [message #580961] Sat, 30 March 2013 08:28 Go to next message
gatetec
Messages: 38
Registered: December 2012
Member
column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30
select 	
	trim(s.sid),
	trim(s.serial#),
        to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
	substr (q.sql_text, 1, 70)
from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece; 


I am running this code, and the output shows multiple lines.

TRIM(S.SID)                              TRIM(S.SERIAL#)                          MINS_RUNNING    SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700                                      46592                                       242.08       Select count(*) as count, case when count(*)>0 then 'FAIL' else
700                                      46592                                       242.08       'PASS' end as result
                                                                                                  from (SELECT cv.code_value
                                                                                                  FROM code_valu

700                                      46592                                       242.08       e cv
                                                                                                  WHERE cv.active_ind = 1
                                                                                                  AND cv.code_set = 200
                                                                                                  AND cv.cod

700                                      46592                                       242.08       e_value > 0
                                                                                                  AND cv.code_value NOT IN(SELECT cv2.code_value FROM

700                                      46592                                       242.08        code_value cv2,order_catalog oc2
                                                                                                  WHERE cv2.code_value= oc2.cat

700                                      46592                                       242.08       alog_cd AND oc2.active_ind  = 1 )
                                                                                                  UNION All
                                                                                                  SELECT Count (ocs

700                                      46592                                       242.08       .catalog_cd)
                                                                                                  FROM order_catalog_synonym ocs
                                                                                                  WHERE ocs.mnemonic

700                                      46592                                       242.08       _type_cd=(SELECT CV.CODE_VALUE
                                                                                                  FROM CODE_VALUE CV
                                                                                                  WHERE CV.cod



SQL_TEXT is VARCHAR2(64)

Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?

Please advise.
Re: Duplicate rows displayed for a column - need to wrap [message #580963 is a reply to message #580961] Sat, 30 March 2013 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set linesize 1000

Regards
Michel
Re: Duplicate rows displayed for a column - need to wrap [message #580965 is a reply to message #580963] Sat, 30 March 2013 08:55 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
set linesize 1000 is not correcting it.

I ran
select * from v$sqltext_with_newlines
. It returns multiple rows already. I think we need to wrap up the SQL_TEXT column in some way.
Please advise.

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------------------------------------------------
070000080C949348 1481657816 bzb4cajc50mfs            3          3 _CD,X.ADMIN_METHOD_CD,X.ADMIN_PT_LOC_CD,X.INITIAL_DOSAGE,X.ADMIN
070000080C949348 1481657816 bzb4cajc50mfs            3          2 DMIN_START_DT_TM,X.ADMIN_END_DT_TM,X.ADMIN_ROUTE_CD,X.ADMIN_SITE
070000080C949348 1481657816 bzb4cajc50mfs            3          1 _FROM_DT_TM,X.VALID_UNTIL_DT_TM,X.ADMIN_NOTE,X.ADMIN_PROV_ID,X.A
070000080C949348 1481657816 bzb4cajc50mfs            3          0 SELECT  /*+  CCL<SHRCCLSRV:S9999:O1:Q00.1> */ X.EVENT_ID,X.VALID
0700000809B29DD0 1016221155 63r3c5hy94mg3            3         14 IVE_IND =     :17
0700000809B29DD0 1016221155 63r3c5hy94mg3            3         13  =     :15   AND PHC3.END_EFFECTIVE_DT_TM >=  :16   AND PHC3.ACT
0700000809B29DD0 1016221155 63r3c5hy94mg3            3         12 PHC3.PFT_HOLD_ID =    PHC2.PFT_HOLD_ID AND PHC3.HOLD_CRITERIA_CD
0700000809B29DD0 1016221155 63r3c5hy94mg3            3         11 D_EFFECTIVE_DT_TM >=  :13   AND PHC2.ACTIVE_IND =     :14   AND
0700000809B29DD0 1016221155 63r3c5hy94mg3            3         10 IA_CD =     :11   AND PHC2.HOLD_CRITERIA =     :12   AND PHC2.EN
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          9  AND PHC2.PFT_HOLD_ID =    PHC1.PFT_HOLD_ID AND PHC2.HOLD_CRITER
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          8 OLD_CRITERIA_CD =     :9    AND PHC1.HOLD_CRITERIA =     :10  ))
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          7 IA_CD =     :7    AND PHC1.HOLD_CRITERIA =     :8   ) OR (PHC1.H
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          6 >=  :5    AND PHC1.ACTIVE_IND =     :6    AND ((PHC1.HOLD_CRITER
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          5 C1.PFT_HOLD_ID =    PH.PFT_HOLD_ID AND PHC1.END_EFFECTIVE_DT_TM
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          4 .HOLD_REASON_CD =     :3    AND PH.ACTIVE_IND =     :4    AND PH
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          3 TITY_ID =     :1    AND PH.PARENT_ENTITY_NAME =     :2    AND PH
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          2 IA PHC2 ,PFT_HOLD_CRITERIA PHC1 ,PFT_HOLD PH  WHERE PH.PARENT_EN
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          1 PHC3.HOLD_CRITERIA FROM  PFT_HOLD_CRITERIA PHC3 ,PFT_HOLD_CRITER
0700000809B29DD0 1016221155 63r3c5hy94mg3            3          0 SELECT  /*+  CCL<PFT_GET_CG_ATTRS_FOR_CHARGE:S9999:O1:Q41.1> */
0700000809C5C1A8 1478249962 a73kjvtc1smga            3          3 ownum <=  :4
0700000809C5C1A8 1478249962 a73kjvtc1smga            3          2 IN (  :1   , :2   )  AND R.CONVERTED_FILE_NAME =     :3    AND r
0700000809C5C1A8 1478249962 a73kjvtc1smga            3          1 _HANDLE_ID FROM  REPORT_QUEUE R  WHERE R.TRANSMISSION_STATUS_CD
0700000809C5C1A8 1478249962 a73kjvtc1smga            3          0 SELECT  /*+  CCL<RRD_CHK_CNVTD_FILES:S9999:O1:Q01.1> */ R.OUTPUT
07000008097A1888 2211991026 4aajsfy1xhmgk            3          6 :2

Re: Duplicate rows displayed for a column - need to wrap [message #580966 is a reply to message #580961] Sat, 30 March 2013 09:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
There are other issues with your query. SQL_ADDRESS does not uniquely identify SQL statement. It must be used with SQL_HASH_VALUE. And some minor stuff like substr (q.sql_text, 1, 70), while sql_text in v$sqltext_with_newlines is only 64 character long. And what is that trim of sid & serial#? Both sid & serial# are NUMBERS, not strings. Anyway, assuming you are on 11.2, use LISTAGG:

column sid format 99999
column serial# format 99999
select
	sid,
	serial#,
        to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
	listagg(q.sql_text) within group(order by q.piece) sql_text
  from      v$session s
        join
            v$sqltext_with_newlines q
          on (
                  s.sql_address = q.address
              and
                  s.sql_hash_value = q.hash_value
             )
  where status='ACTIVE'
    and type <>'BACKGROUND'
    and last_call_et> 60
  group by s.sid,
           s.serial#,
           q.address,
           q.hash_value,
           s.last_call_et
  order by s.sid,
           s.serial#,
           q.address,
           q.hash_value
/


SY.

[Updated on: Sat, 30 March 2013 09:07]

Report message to a moderator

Re: Duplicate rows displayed for a column - need to wrap [message #580968 is a reply to message #580966] Sat, 30 March 2013 09:12 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
Thank you for the catch!!

I am on 10g. How do you tweak this query for 10g?

        listagg(q.sql_text) within group(order by q.piece) sql_text
                                   *
ERROR at line 5:
ORA-00923: FROM keyword not found where expected

Re: Duplicate rows displayed for a column - need to wrap [message #580970 is a reply to message #580968] Sat, 30 March 2013 09:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
gatetec wrote on Sat, 30 March 2013 10:12
I am on 10g.


set long 100000
select  sid,
        serial#,
        to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
        dbms_xmlgen.convert(
                            xmlagg(
                                   xmlelement(e,q.sql_text).extract('//text()')
                                   order by q.piece
                                  ).GetClobVal(),
                            1
                           ) sql_text
  from      v$session s
        join
            v$sqltext_with_newlines q
          on (
                  s.sql_address = q.address
              and
                  s.sql_hash_value = q.hash_value
             )
  where connect_by_isleaf = 1
    and status='ACTIVE'
    and type <>'BACKGROUND'
    and last_call_et> 60
  group by s.sid,
           s.serial#,
           q.address,
           q.hash_value,
           s.last_call_et
  order by s.sid,
           s.serial#,
           q.address,
           q.hash_value
/


SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580971 is a reply to message #580970] Sat, 30 March 2013 10:07 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
                                   xmlelement(e,q.sql_text).extract('//text()')
                                                                   *
ERROR at line 6:
ORA-01788: CONNECT BY clause required in this query block


Thank you, SY!

How do you resolve this?
Re: Duplicate rows displayed for a column - need to wrap [message #580972 is a reply to message #580971] Sat, 30 March 2013 10:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
Post complete query and complete version.

SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580973 is a reply to message #580972] Sat, 30 March 2013 10:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
Oops,

I posted wrong query. Should be:

set long 100000
select  sid,
        serial#,
        to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
        dbms_xmlgen.convert(
                            xmlagg(
                                   xmlelement(e,q.sql_text).extract('//text()')
                                   order by q.piece
                                  ).GetClobVal(),
                            1
                           ) sql_text
  from      v$session s
        join
            v$sqltext_with_newlines q
          on (
                  s.sql_address = q.address
              and
                  s.sql_hash_value = q.hash_value
             )
  where status='ACTIVE'
    and type <>'BACKGROUND'
    and last_call_et> 60
  group by s.sid,
           s.serial#,
           q.address,
           q.hash_value,
           s.last_call_et
  order by s.sid,
           s.serial#,
           q.address,
           q.hash_value
/


SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580974 is a reply to message #580973] Sat, 30 March 2013 10:17 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
ERROR:
ORA-31011: XML parsing failed

no rows selected



Thank you, SY! It is not returning rows. I checked.!

[Updated on: Sat, 30 March 2013 10:18]

Report message to a moderator

Re: Duplicate rows displayed for a column - need to wrap [message #580977 is a reply to message #580974] Sat, 30 March 2013 10:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
Most likely SQL_TEXT contains characters XML doesn't support. So the only other solution I can think of is CLOBAGG:

create or replace
  type clobagg_type as object(
                              text clob,
                              static function ODCIAggregateInitialize(
                                                                      sctx in out clobagg_type
                                                                     )
                                return number,
                              member function ODCIAggregateIterate(
                                                                   self  in out clobagg_type,
                                                                   value in     clob
                                                                  )
                                return number,
                              member function ODCIAggregateTerminate(
                                                                     self        in     clobagg_type,
                                                                     returnvalue    out clob,
                                                                     flags       in     number
                                                                    )
                                return number,
                              member function ODCIAggregateMerge(
                                                                 self in out clobagg_type,
                                                                 ctx2 in     clobagg_type
                                                                )
                                return number
                             );
/ 
create or replace
  type body clobagg_type
    is
      static function ODCIAggregateInitialize(
                                              sctx in out clobagg_type
                                             )
        return number
        is
        begin
            sctx := clobagg_type(null) ;
            return ODCIConst.Success ;
      end;
      member function ODCIAggregateIterate(
                                           self  in out clobagg_type,
                                           value in     clob
                                          )
        return number
        is
        begin
            self.text := self.text || value ;
            return ODCIConst.Success;
      end;
      member function ODCIAggregateTerminate(
                                             self        in     clobagg_type,
                                             returnvalue    out clob,
                                             flags       in     number
                                            )
        return number
        is
        begin
            returnValue := self.text;
            return ODCIConst.Success;
        end;
      member function ODCIAggregateMerge(
                                         self in out clobagg_type ,
                                         ctx2 in     clobagg_type
                                        )
        return number
        is
        begin
            self.text := self.text || ctx2.text;
            return ODCIConst.Success;
        end;
end;
/ 
create or replace
  function clobagg(
                   input clob
                  )
    return clob
    deterministic
    parallel_enable
    aggregate using clobagg_type;
/ 


Now you can:

set long 100000
with t as (
           select  sid,
                   serial#,
                   to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
                   clobagg(sql_text) over(
                                          partition by sid,serial#,address,hash_value
                                          order by piece
                                         ) sql_text,
                   row_number() over(
                                     partition by sid,serial#,address,hash_value
                                     order by piece desc
                                    ) rn
             from      v$session s
                   join
                       v$sqltext_with_newlines q
                     on (
                             s.sql_address = q.address
                         and
                             s.sql_hash_value = q.hash_value
                        )
             where status='ACTIVE'
               and type <>'BACKGROUND'
               and last_call_et> 60
          )
select  sid,
	serial#,
        mins_running,
	sql_text
  from  t
  where rn = 1
  order by sid,
           serial#
/


SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580979 is a reply to message #580977] Sat, 30 March 2013 10:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
BTW,

Why not to use V$SQLAREA which already has SQL_FULLTEXT:

select  sid,
	serial#,
        to_char(nvl(s.last_call_et/60,0),99999.99) mins_running,
	sql_fulltext
  from      v$session s
        join
            v$sqlarea q
          on (
                  s.sql_address = q.address
              and
                  s.sql_hash_value = q.hash_value
             )
  where status='ACTIVE'
    and type <>'BACKGROUND'
    and last_call_et> 60
  order by s.sid,
           s.serial#,
           q.address,
           q.hash_value
/


SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580981 is a reply to message #580979] Sat, 30 March 2013 11:34 Go to previous messageGo to next message
gatetec
Messages: 38
Registered: December 2012
Member
column SID format 'a10'
column SRL format 'a10'
column M_R format 'a10'
column SQL_FULL_TXT format 'a170'
set linesize 1000
set pagesize 30

select  to_char(s.sid, 9999999) SID,
	to_char(s.serial#, 9999999) SRL,
        to_char(nvl(s.last_call_et/60,0),99999.99) M_R,
	sql_fulltext SQL_FULL_TXT
  from      v$session s
        join
            v$sqlarea q
          on (
                  s.sql_address = q.address
              and
                  s.sql_hash_value = q.hash_value
             )
  where status='ACTIVE'
    and type <>'BACKGROUND'
    and last_call_et> 2
  order by s.sid,
           s.serial#,
           q.address,
           q.hash_value



Yes, thank you for the correction. It works fine. One more thing is that sql_fulltext output is chopped off, so that I can't see all of the content...
Please advise.

SID        SRL        M_R        SQL_FULL_TXT
---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1114      35769        7.52  SELECT  /*+  CCL<ADV_LDS_PHA_RPT_VC_MAR7:S9999:O1:q03.1> */ O.TEMPLATE_ORDER_ID,
    1403      14434         .18  select  to_char(s.sid, 9999999) SID,
                                        to_char(s.serial#, 9999999) SRL,
                                         t

    1567      13504         .52  SELECT  /*+  CCL<VC_DOWNTIME_CENSUS:S9999:O1:q02.1> */ EA.ENCNTR_ALIAS_TYPE_CD,E

Re: Duplicate rows displayed for a column - need to wrap [message #580985 is a reply to message #580981] Sat, 30 March 2013 11:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1955
Registered: January 2010
Senior Member
You muissed:

set long 100000


You might also want to take a look at SET LONGCHUNKSIZE.

SY.
Re: Duplicate rows displayed for a column - need to wrap [message #580989 is a reply to message #580985] Sat, 30 March 2013 12:09 Go to previous message
gatetec
Messages: 38
Registered: December 2012
Member
Really appreciate it! That's working perfect! Many thanks! SY.
Previous Topic: Bulk Collect
Next Topic: Declaring a record type in a package spec ?
Goto Forum:
  


Current Time: Thu Jul 24 21:03:24 CDT 2014

Total time taken to generate the page: 0.60128 seconds