Managing Distributed Transactions

Hi all,
I have an application throwing the following error :ORA-01591: lock held by in-doubt distributed transaction

I checked DBA_2PC_PENDING and dba_2pc_neighbors and found 2 transaction in PENDING state.

I wasn't sure how to deal with these, so googled. I found a site that causiously prescibed deleting as follows:

SQL> select state, local_tran_id from dba_2pc_pending;

STATE LOCAL_TRAN_ID
---------------- ----------------------

prepared         10.15.4693
prepared         4.18.4935

SQL> delete from dba_2pc_pending where local_tran_id='10.15.4693';

1 row deleted.

SQL> delete from pending_sessions$ where local_tran_id='10.15.4693';

1 row deleted.

SQL> delete from pending_sub_sessions$ where local_tran_id='10.15.4693';

0 rows deleted.

commit;

I did not set transaction use SYSTEM before making the deletes ignore the error that the operation was illigal in managed undo mode. I though I could get away with an error as this is a test database. But I do not want to bounce it as this affects too many people.

I would like to clear up what I now think was a mistake... I think I should have done a COMMIT|ROLLBACK force '10.15.4693'; first.

I think I need to SET TRANSACTION USE ROLLBACK SEGMENT SYSTEM; to run any of the tidy up commands I've found, but I can't as I'm using Automatic Undo mode.

Anyone know how I can fix this problem... oh yes the applcaition still fails with the origonal error including origonal trans id, but DBA_2PC_PENDING and dba_2pc_neighbors show no rows.

Help appreciated,

Cheers
Tony

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 01 2004 - 08:25:03 CST