How to detect and cleanup pending transactions in Oracle
2023-07-06
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 ‘’;
or
ROLLBACK FORCE ‘’;
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();