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 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

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