Manually Resolving In-Doubt Transactions: Different Scenarios

NOTE1: If using Oracle 9i and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use the following workaround
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

select * from dba_2pc_pending
/

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 prepared no

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/ch4e.htm

COMMIT FORCE '3.7.99084';

SQL> select LOCAL_TRAN_ID, STATE, MIXED, ADVICE from dba_2pc_pending;

LOCAL_TRAN_ID STATE MIX A
---------------------- ---------------- --- -
3.7.99084 forced commit no

SQL> select * from dba_pending_transactions;

FORMATID
----------
GLOBALID
--------------------------------------------------------------------------------
BRANCHID
--------------------------------------------------------------------------------
48801
34A257C2BC134A007FFD
73616D705841436F6E6E506F6F6C

alter session set "_smu_debug_mode" = 4;

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.7.99084');

SQL> select * from dba_pending_transactions;

no rows selected

SQL> select * from dba_pending_transactions;

no rows selected