How to check the pending transaction in your oracle session?

advertisements

_____________________________________________________________________________________________________________________

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

--------------------

_____________________________________________________________________________________________________________________

Website Stats