Oracle: How to find out if there is a transaction pending?
you can check if your session has a row in V$TRANSACTION
(obviously that requires read privilege on this view):
SQL> 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;
COUNT(*)
----------
0
SQL> insert into a values (1);
1 row inserted
SQL> 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;
COUNT(*)
----------
1
SQL> commit;
Commit complete
SQL> 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;
COUNT(*)
----------
0
This is the query I normally use,
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;
SELECT * FROM V$TRANSACTION
WHERE STATUS='ACTIVE';
See: http://forums.oracle.com/forums/thread.jspa?threadID=691061
Use the query below to find out pending transaction.
If it returns a value, it means there is a pending transaction.
Here is the query:
select dbms_transaction.step_id from dual
;
References:
http://www.acehints.com/2011/07/how-to-check-pending-transaction-in.html
http://www.acehints.com/p/site-map.html