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.

Oracle longops

Oracle has so many amazing features, and one of them I learned about yesterday was longops. It keeps track of all long operations within a database. You can view them like this (with the associated SQL):

select
  vl.*, vsql.sql_fulltext
from
  v$session_longops vl,
  v$sqlarea vsql
where
  vl.sql_id = vsql.sql_id
  AND
  vl.sql_id = vsql.sql_id
ORDER BY
  vl.elapsed_seconds desc
;

Now if you want to create an alert to let you know when there are current long operations in your system you can do something like this:

select
  vl.*, vsql.sql_fulltext
from
  v$session_longops vl,
  v$sqlarea vsql
where
  vl.sql_id = vsql.sql_id
  AND
  vl.username = 'SOMEUSER'
  AND
  vl.time_remaining > 30
  AND
  vl.elapsed_seconds > 30
;

This will tell you if you have any current long operations that have been going for longer than 30 seconds, and have more than 30 seconds left to go.

You will likely want to use the vl.username where statement to filter out system events.

Thanks to Russ for this query.