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

OS: Red Hat Enterprise Linux Server release 5.3

Oracle: 11.1.0.7

One a the application support guy asked me for help.

He received the error

Error starting at line 3 in command:
update scv_cv_publish_xml set status = 'A' where status = 'V' and verwerking_status='A'
Error report:
SQL Error: ORA-01591: lock held by in-doubt distributed transaction 63.8.92859
01591. 00000 -  "lock held by in-doubt distributed transaction %s"
*Cause:    Trying to access resource that is locked by a dead two-phase commit
           transaction that is in prepared state.
*Action:   DBA should query the pending_trans$ and related tables, and attempt
           to repair network connection(s) to coordinator and commit point.
           If timely repair is not possible, DBA should contact DBA at commit
           point if known or end user for correct outcome, or use heuristic
           default if given to issue a heuristic commit or abort command to
           finalize the local portion of the distributed transaction.

So, first look in the database.

SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859'; LOCAL_TRAN_ID       STATE ---------------------- ---------------- 63.8.92859                prepared SQL> select a.sql_text, s.osuser, s.username      from v$transaction t, v$session s, v$sqlarea a where s.taddr = t.addr      and a.address = s.prev_sql_addr      and t.xidusn = 63      and t.xidslot = 8      and t.xidsqn = 92859; no rows selected

Ups. Hmm. Metalink help me, please.
Bingo !

How To Resolve Stranded DBA_2PC_PENDING Entries [ID 401302.1]

SQL>  SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */             KTUXESTA Status,             KTUXECFL Flags             FROM x$ktuxe             WHERE ktuxesta!='INACTIVE'             AND ktuxeusn= 63;   KTUXEUSN   KTUXESLT KTUXESQN STATUS  FLAGS ---------- ---------- ---------- ---------------- ------------------------ 63    8   92859 PREPARED  SCO|COL|REV|DEAD|EXTDTX SQL> rollback force '63.8.92859'; Rollback complete. SQL> commit; Commit complete. SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859'; LOCAL_TRAN_ID       STATE ---------------------- ---------------- 63.8.92859                forced rollback

Unfortunately it didn`t help.

SQL> delete from sys.pending_trans$ where local_tran_id='63.8.92859'; 1 row deleted. sys@avac> delete from sys.pending_sessions$ where local_tran_id ='63.8.92859'; 1 row deleted. sys@avac> delete from sys.pending_sub_sessions$ where local_tran_id = '63.8.92859'; 0 rows deleted. sys@avac> commit; Commit complete. sys@avac> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859'; no rows selected

Solved.