to select running transactions longer than one hour
SELECT s.osuser, vp.spid as os_pid, S.BLOCKING_SESSION blocker, S.SID, S.SERIAL#, S.USERNAME, S.MACHINE, Q.SQL_FULLTEXT cur_sql, PQ.SQL_FULLTEXT prev_sql, vt.used_urec, vt.start_date FROM v$session S LEFT JOIN v$sqlarea Q on S.SQL_ID = Q.SQL_ID LEFT JOIN v$sqlarea PQ on S.PREV_SQL_ID = PQ.SQL_ID LEFT JOIN v$process vp on s.paddr = vp.addr LEFT JOIN v$transaction vt on s.saddr = vt.ses_addr WHERE vt.start_date < SYSDATE - (1/24) ORDER BY S.SID;
Comments
Post a Comment