Categories
Oracle DB Uncategorized

ORA-01591 – How to remove blocking distributed transaction?

  • Post author By admin
  • Post date

Sometimes when we want do get access to table we will see this error:

ORA-01591: lock held by in-doubt distributed transaction 5.32.1144223

This usually happens when we lose connection to a remote database for some reason. You need to repeat it on both databases.

To check pending transaction take this:

SELECT * FROM DBA_2PC_PENDING WHERE state = 'prepared';

If you want to rollback this transaction take this steps:

  1. We need to temporarily disable RECO to resolve the questionable transaction manually.
<SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

2. Try to rollback transaction.


<SQL> EXEC DBMS_TRANSACTION.rollback_force('TRANSACTION_ID')

<SQL> COMMIT;

3. Enable RECO two-phase approval

<SQL> ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

4. Now you can to check is transaction is rollabcked suckessfully:

SELECT * FROM DBA_2PC_PENDING WHERE state = 'prepared';

5. If you sill see this transaction you must delete it.

<SQL> EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('TRANSACTION_ID');

<SQL> COMMIT;