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

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;
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google