Day: October 23, 2023

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

SQL>  select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID
----------------------
1.23.456789

SQL> select local_tran_id, state,fail_time,force_time, retry_time 
     from dba_2pc_pending;

LOCAL_TRAN_ID  STATE   FAIL_TIME   RETRY_TIME  MIXED
------------   -----   ---------   ----------  -----
1.23.456789    prepared 1-OCT-23   22-OCT-23    no
SQL> rollback force '1.23.456789';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry( '1.23.456789');
BEGIN dbms_transaction.purge_lost_db_entry( '1.23.456789'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 74
ORA-06512: at "SYS.DBMS_TRANSACTION", line 96
ORA-06512: at line 1

SQL>exec dbms_transaction.purge_lost_db_entry('1.23.456789');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected