LLI Wiki — My Digital Knowledge Base

Oracle: find open transactions and their sessions

Queries on v$transaction and v$session to list active transactions.

Two queries to find open transactions in an Oracle database and the sessions that own them.

Active transactions and their sessions

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;

used_ublk and used_urec show the number of undo blocks and undo records used by the transaction.

Does the current session have an open transaction?

SELECT COUNT(*)
  FROM v$transaction t, v$session s, v$mystat m
 WHERE t.ses_addr = s.saddr
   AND s.sid = m.sid
   AND ROWNUM = 1;

The result is 1 if the current session has an active transaction, 0 otherwise.

Mastodon LLIMastodon SocialFosstodon