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.