For all / any that are interested Oracle Support fixed my problem by inserting dummy rows into pending_trans$ and pending_sessions$ They recommend the following note, but none of the scenarios here require what they advocated in our case: Note: 126069.1 Manually Resolving In-Doubt Transactions but anyway, here's what happened: SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 2 KTUXESTA Status, 3 KTUXECFL Flags 4 FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 4; KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS ---------- ---------- ---------- ---------------- ------------------------ 4 18 4935 PREPARED SCO|COL|REV|DEAD SQL> insert into pending_trans$ 2 ( LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME) 3 values( '4.18.4935',306206, /* */ 'XXXXXXX.12345.1.2.3', /* These values can be used without any */ 4 'prepared','P', /* modification. Most of the values are */ 5 hextoraw( '00000001' ), /* constant. */ 6 hextoraw( '00000000' ), /* */ 7 0, sysdate, sysdate ); 1 row created. SQL> insert into pending_sessions$ values('4.18.4935',1, 2 hextoraw('05004F003A1500000104'),'C',0,30258592,'',146); 1 row created. commit; SQL> select * from pending_sessions$ LOCAL_TRAN_ID SESSION_ID ---------------------- ---------- BRANCH_ID -------------------------------------------------------------------------------- I TYPE# PARENT_DBID - ---------- ---------------- PARENT_DB -------------------------------------------------------------------------------- DB_USERID ---------- 4.18.4935 1 05004F003A1500000104 C 0 30258592 146 SQL> select * from pending_trans$; LOCAL_TRAN_ID GLOBAL_TRAN_FMT ---------------------- --------------- GLOBAL_ORACLE_ID ---------------------------------------------------------------- GLOBAL_FOREIGN_ID -------------------------------------------------------------------------------- TRAN_COMMENT -------------------------------------------------------------------------------- STATE S H SESSION_ RECO_VEC TYPE# FAIL_TIME ---------------- - - -------- -------- ---------- ------------------ HEURISTIC_TIME RECO_TIME TOP_DB_USER ------------------ ------------------ ------------------------------ TOP_OS_USER ---------------------------------------------------------------- TOP_OS_HOST -------------------------------------------------------------------------------- TOP_OS_TERMINAL -------------------------------------------------------------------------------- GLOBAL_COMMIT# SPARE1 SPARE2 SPARE3 ---------------- ---------- ------------------------------ ---------- SPARE4 ------------------------------ 4.18.4935 306206 XXXXXXX.12345.1.2.3 prepared P 00000001 00000000 0 10-FEB-05 10-FEB-05 SQL> rollback force '4.18.4935'; Rollback complete. SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935'); BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935'); END; * ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode ORA-06512: at "SYS.DBMS_TRANSACTION", line 65 ORA-06512: at "SYS.DBMS_TRANSACTION", line 85 ORA-06512: at line 1 SQL> select count(*) from tracking.tracked_lacosa_backend_request; select count(*) from tracking.tracked_lacosa_backend_request * ERROR at line 1: ORA-01591: lock held by in-doubt distributed transaction 10.15.4693 SQL> alter session set "_smu_debug_mode" = 4; Session altered. SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935'); PL/SQL procedure successfully completed. SQL> select count(*) from tracking.tracked_lacosa_backend_request; select count(*) from tracking.tracked_lacosa_backend_request * ERROR at line 1: ORA-01591: lock held by in-doubt distributed transaction 10.15.4693 ----************* Notice here the transaction id changed ************* ----- SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 2 KTUXESTA Status, 3 KTUXECFL Flags 4 FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10; KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS ---------- ---------- ---------- ---------------- ------------------------ 10 15 4693 PREPARED SCO|COL|REV|DEAD SQL> insert into pending_trans$ ( LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID, 2 STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME) 3 values( '10.15.4693', 4 306206, /* */ 5 'XXXXXXX.12345.1.2.3', /* These values can be used without any */ 6 'prepared','P', /* modification. Most of the values are */ 7 hextoraw( '00000001' ), /* constant. */ 8 hextoraw( '00000000' ), /* */ 9 0, sysdate, sysdate ); 1 row created. SQL> insert into pending_sessions$ values('10.15.4693',1, 2 hextoraw('05004F003A1500000104'),'C',0,30258592,'',146); 1 row created. SQL> commit; Commit complete. SQL> rollback force '10.15.4693'; Rollback complete. SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.15.4693'); PL/SQL procedure successfully completed. SQL> select count(*) from tracking.tracked_lacosa_backend_request; COUNT(*) ---------- 98697 Yee Ha! Tony "Powell, Mark D" <mark.powell@xxxxxxx> 02/09/2005 02:13 PM To "'Tony.Adolph@xxxxxx'" <Tony.Adolph@xxxxxx>, oracle-l@xxxxxxxxxxxxx cc Subject RE: lock held by in-doubt distributed transaction Is tracking.tracked_lacosa_request a local table or remote? If remote did you check the dba_pending* views on the remote system. The bug I was thinking of showed entries in the pending views but your problem does not so it is likely a different bug. I checked metalink for the ORA-01591error and got around 47 hits. I glanced at a few but I did not see a perfect match. I think you will need to open an iTAR and get help directly from Oracle. Please let the board know how this is resolved. HTH -- Mark D Powell -- -----Original Message----- From: Tony.Adolph@xxxxxx [mailto:Tony.Adolph@xxxxxx] Sent: Wednesday, February 09, 2005 3:37 AM To: mark.powell@xxxxxxx Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx Subject: RE: lock held by in-doubt distributed transaction Hi Mark, Thanks for the feedback. I tried... SQL> alter session set "_smu_debug_mode" = 4; Session altered. SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935'); BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END; * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_TRANSACTION", line 94 ORA-06512: at line 1 :-( Tony "Powell, Mark D" <mark.powell@xxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 02/08/2005 06:48 PM Please respond to mark.powell@xxxxxxx To oracle-l@xxxxxxxxxxxxx cc Subject RE: lock held by in-doubt distributed transaction I believe this is a bug. The first solution I saw was support telling people to convert their systems to using regular rollback segments but I believe that you can issue ' alter session set "_smu_debug_mode" = 4; ' to get around the problem. I do not have the bug number so before you alter your session you may want to check metalink on the hidden parameter. HTH -- Mark D Powell -- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Tony.Adolph@xxxxxx Sent: Tuesday, February 08, 2005 11:55 AM To: oracle-l@xxxxxxxxxxxxx Cc: Maxim.Demenko@xxxxxx Subject: lock held by in-doubt distributed transaction Hi folks, One of our developers has just reported this error after a simple select: select COUNT(*) FROM tracking.tracked_lacosa_request * ERROR at line 1: ORA-01591: lock held by in-doubt distributed transaction 4.18.4935 So I looked for the in doubt transaction as follows: select * from dba_2pc_neighbors; and select * from dba_2pc_pending; but both returned no rows. I tried the same query and also get the same error. I bounced the database (immediate) and still get the error and all the time with the same transaction id 4.18.4935. I got the developers to bounce their jboss but no joy. I tried dbms_transaction.purge_lost_db_entry: SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935'); BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END; * ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode ORA-06512: at "SYS.DBMS_TRANSACTION", line 65 ORA-06512: at "SYS.DBMS_TRANSACTION", line 85 ORA-06512: at line 1 Any ideas folks? Cheers Tony -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l