How to check the pending transaction in your oracle session?

There is one function step_id available with DBMS_TRANSACTION package. This function returns local unique positive integer that orders the DML operations of a transaction. If the function returns any positive number that mean you have a pending transaction in your session. Otherwise it returns null.


Syntax:
DBMS_TRANSACTION.STEP_ID
   RETURN NUMBER;

Parameters: None

Examples:
SQL> update emp2 set age=42 where name='James';

1 row updated.
SQL> select dbms_transaction.step_id from dual;

             STEP_ID
--------------------
    8895068397326636
SQL> update dept set DNAME ='FINANCE' where DEPTNO=10;

1 row updated.
SQL> select dbms_transaction.step_id from dual;

             STEP_ID
--------------------
    8895068397326637
SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

             STEP_ID
--------------------

SQL> update dept set DNAME ='FINANCE' where DEPTNO=10;

1 row updated.

SQL> select dbms_transaction.step_id from dual;

             STEP_ID
--------------------
    5195211769436690

SQL>  update emp2 set age=42 where name='James';

1 row updated.

SQL>  select dbms_transaction.step_id from dual;

             STEP_ID
--------------------
    5195211769436691

SQL> commit;

Commit complete.

SQL>  select dbms_transaction.step_id from dual;

             STEP_ID
--------------------

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google