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

User · Answer

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

User · Answer

SELECT   FROM V TRANSACTION WHERE STATUS  ACTIVE     See  http   forums oracle com forums thread jspa threadID 691061

User · Answer

you can check if your session has a row in V TRANSACTION  obviously that requires read privilege on this view    SQL gt  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 gt  insert into a values  1    1 row inserted  SQL gt  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 gt  commit   Commit complete  SQL gt  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

User · Answer

The easiest and most reliable solution is to try and start a transaction and see it if succeeds  If some code already started a transaction but has not yet issued any DML  then the V TRANSACTION view won t show anything   In this example below  I handle the exception to raise a user-defined application error  To defer to an existing exception handler  just do a SET TRANSACTION and then immediately COMMIT to undo it   DECLARE     transaction in progress EXCEPTION      PRAGMA EXCEPTION INIT transaction in progress  -1453   BEGIN     SET TRANSACTION NAME  CHECK FOR TRANSACTION ALREADY SET       COMMIT  -- end transaction EXCEPTION     WHEN transaction in progress THEN         RAISE APPLICATION ERROR -20000  Transaction is already in progress    END

User · Answer

Also see     How can I tell if I have uncommitted work in an Oracle transaction

User · Answer

Matthew Watson can be modified to be used in RAC  select t inst id          s sid        s serial         s username        s machine        s status        s lockwait        t used ublk        t used urec        t start time from gv transaction t inner join gv session s on t addr   s taddr

User · Answer

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

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

Examples related to oracle

Examples related to transactions