Oracle: roll back in-doubt distributed transactions (ORA-01591)
List prepared 2PC transactions in DBA_2PC_PENDING and generate rollback force commands.
When a distributed transaction is left in doubt, Oracle reports ORA-01591 (lock held by in-doubt distributed transaction). Pending two-phase-commit transactions are listed in DBA_2PC_PENDING.
List the prepared transactions
select * from DBA_2PC_PENDING where state='prepared';
Generate the rollback commands
select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';
Example of generated commands (the transaction ids are illustrative):
rollback force '21.10.1693789';
rollback force '16.21.2392766';
Notes
- Forcing a rollback decides the outcome locally: make sure the other nodes of the distributed transaction did not commit it.