Query Behind SQL Session

Get Full SQL From Oracle SID/OS PID (Find Long running oracle PIDs)


set lines 200 pages 200
col SQL_TEXT for a65
col "SID|SERIAL|PROCESS" for a15
col USERNAME for a9
col OSUSER for a14
col MACHINE for a18
col PROGRAM for a22
col COMMAND for a7
col TERMINAL for a20
col MODULE for a20
BREAK ON "SID|SERIAL|PROCESS" SKIP PAGE ON USERNAME SKIP 1 ON OSUSER SKIP 1 ON MACHINE SKIP 1 ON PROGRAM SKIP 1 ON COMMAND SKIP 1

select a.SQL_TEXT
,s.sid||'|'||s.serial#||'|'||p.spid "SID|SERIAL|PROCESS"
,s.USERNAME
--,s.STATUS,
,s.OSUSER
,s.MACHINE
--,s.TERMINAL
,s.PROGRAM
--,s.MODULE
--,s.ACTION
,ct.NAME Command
FROM v$session s, v$process p, v$sqltext a, audit_actions ct
WHERE s.paddr = p.addr
AND p.background is null
AND a.COMMAND_TYPE = ct.ACTION
AND a.ADDRESS=s.SQL_ADDRESS
and a.HASH_VALUE=s.SQL_HASH_VALUE
and s.sid in (33,116)                                                   -- Provide Oracle SID
--and p.spid in (25752,2589,26349)                          -- Provide OS Process ID From TOP/GLANCE
order by s.SID,a.PIECE;