How to detect and cleanup pending transactions in Oracle?

Usually pending transactions clear up on their own. If not, read on.If you have a pending transaction that will not go away then check the pending states with the following query:SELECt local_tran_id, state, mixedFROM dba_2pc_pending;Pending transactions can be in any of the following states :collecting, committed, prepared, forced commit, forced rollbackIf the transaction is in the ‘prepared’ state then runCOMMIT FORCE ‘<local_tran_id>’;orROLLBACK FORCE ‘<local_tran_id>’;If the transaction is any state (other than ‘prepared’) and the transaction is not resolving then Log in as ‘SYS’ and run the following scriptexec dbms_transaction.purge_lost_db_entry(<local_tran_id>);

Usually pending transactions clear up on their own. If not, read on.

If you have a pending transaction that will not go away then check the pending states with the following query:

SELECt local_tran_id, state, mixed

FROM dba_2pc_pending;

Pending transactions can be in any of the following states :

collecting, committed, prepared, forced commit, forced rollback

If the transaction is in the ‘prepared’ state then run

COMMIT FORCE ‘<local_tran_id>’;

or

ROLLBACK FORCE ‘<local_tran_id>’;

If the transaction is any state (other than ‘prepared’) and the transaction is not resolving then Log in as ‘SYS’ and run the following script

exec dbms_transaction.purge_lost_db_entry(<local_tran_id>);