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

Find and replace with sed command in Linux

Find and replace feature is always handy. It can turn into a torture when it comes to change or delete a simple constant string in a text file. There is a handy tool in linux for doing these kind of tihngs. Actually sed is not a text editor but it is used outside of the text file to make changes.