ORA-01591 lock held by in-doubt distributed transaction

dbtut

6 years ago

With the DB link, you can get an ORA-01591 error if you join the tables in the queried database with the tables in a remote database. This error occurs because there are hanging processes.

In the queried database, transactions that appear in the “prepared” state in the DBA_2PC_PENDING view must be committed or rollback.

You can query information about pending transactions from the following tables and views.

SYS.PENDING_TRANS$
SYS.DBA_2PC_PENDING
SYS.DBA_2PC_NEIGHBORS

Preparing rollback commands for pending processes:

The following query can prepare rollback force commands for pending transactions.

SQL> select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

rollback force '10132.14.225761';
rollback force '10132.29.12864';
rollback force '10133.21.130785';
rollback force '10133.22.350978';
rollback force '10133.4.142261';

SQL> select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

rollback force '10132.14.225761';

rollback force '10132.29.12864';

rollback force '10133.21.130785';

rollback force '10133.22.350978';

rollback force '10133.4.142261';

Preparing commit commands for pending operations:

The following query can prepare commit force commands for pending transactions. The commit process may not always complete successfully.

SQL> select 'rollback commit '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

commit force '10132.14.225761';
commit force '10132.29.12864';
commit force '10133.21.130785';
commit force '10133.22.350978';
commit force '10133.4.142261';

SQL> select 'rollback commit '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

commit force '10132.14.225761';

commit force '10132.29.12864';

commit force '10133.21.130785';

commit force '10133.22.350978';

commit force '10133.4.142261';