Thursday, May 29, 2008

Oracle open transactions

Here is another neat Oracle query you can run that will give you any open transactions:


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 - (5/1440)
-- AND
-- s.machine = 'machine.name'
ORDER BY
S.SID
;


With this query, you can track down any open transactions to their Oracle subprocess PID on the operating system. Any transaction that has been running for longer than five minutes will show up with this query.

Now if you wanted to create an alert (I love alerts) that will tell you when you have an open transaction for more than five minutes:


select
LTRIM(COUNT(1))
from
v$transaction
where
start_date < SYSDATE - (5/1440)
;


Thanks to Ted for this query.

2 comments:

Anonymous said...

aaahhh.... if only I'd have known in time....

jmilliron said...

Found this on Google. Thanks!