Oracle: How to find out if there is a transaction pending?

Question

I'm looking for a way to find out if there are uncommited INSERT, UPDATE or DELETE statements in the current session. One way would be to check v$lock with the current sid, but that requires read access to v$lock, which is be a problem if the DBA doesn't want to grant it. Any other ways (other than keeping track of all database commands issued by the application)?

Answer 1

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

See: http://forums.oracle.com/forums/thread.jspa?threadID=691061

Answer 2

Hi ammoQ,

you can check if your session has a row in V$TRANSACTION (obviously that requires read privilege on this view):

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

  COUNT(*)
----------
         0

SQL> insert into a values (1);

1 row inserted

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

  COUNT(*)
----------
         1

SQL> commit;

Commit complete

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

  COUNT(*)
----------
         0

Answer 3

Also see...

http://stackoverflow.com/questions/506456/how-can-i-tell-if-i-have-uncommitted-work-in-an-oracle-transaction

Answer 4

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;