Skip to main content

Find running transactions longer than one hour

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

Popular posts from this blog