LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon