Oracle Query to find out the SQL Text Using the Unix Process ID SPID, SID and for Long Running Sessions

advertisements

_____________________________________________________________________________________________________________________

If you have process id (SPID) with use following query

set pages 1000

set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S

WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND     s.sid in (select s.sid from v$session s , v$process p
where   p.addr =s.paddr
and     p.spid in (&ProcessID)) ORDER BY S.SID, T.PIECE;

If you have SID with you use following query

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S

WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND     s.sid in (&sid) ORDER BY S.SID, T.PIECE;

If you wanted to find out the sql text for all the long running sessions then use following query.
set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT      s.sid, s.username, T.SQL_TEXT

FROM v$SQLTEXT T, v$SESSION S
WHERE       S.SQL_ADDRESS=T.ADDRESS
and   s.status ='ACTIVE'
AND   s.sid in (select sid  from v$session_longops where time_remaining>0)
ORDER BY S.SID, T.PIECE;

_____________________________________________________________________________________________________________________

Website Stats