The first uses v$sqltext to query executing SQL:
SELECT vs.username,
vs.sid,
vs.serial#,
vs.machine,
vst.sql_text,
LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':'
||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':'
||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) runtime
FROM v$session vs,
v$sqltext vst
WHERE vs.status = 'ACTIVE'
AND vs.username IS NOT NULL
AND vs.sql_address = vst.address
AND vs.sql_hash_value = vst.hash_value
AND vs.audsid != USERENV('SESSIONID')
ORDER BY vs.last_call_et DESC,
vs.username,
vst.piece
;
This one uses v$sqlarea and will get PL/SQL and SQL:SELECT vs.username,
vs.sid,
vs.serial#,
vs.machine,
vsa.optimizer_mode,
vsa.sql_text,
LTRIM(TO_CHAR(FLOOR(vs.last_call_et/3600),'00'))||':'
||LTRIM(TO_CHAR(FLOOR(MOD(vs.last_call_et,3600)/60),'00'))||':'
||LTRIM(TO_CHAR(MOD(vs.last_call_et,60),'00')) last_call
FROM v$session vs,
v$sqlarea vsa
WHERE vs.sql_hash_value = vsa.hash_value
AND vs.sql_address = vsa.address
AND vs.audsid != USERENV('SESSIONID')
ORDER BY vs.last_call_et DESC,
vs.username
;
Done.
Posts
