Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Managing Distributed Transactions
Hi Tony
There is article on Metalink:
Doc ID Note:126069.1 Manually Resolving In-Doubt Transactions: Different Scenarios
I would believe that you will find some valuable information there.
HTH Kresimir Fabijanic
Tony.Adolph_at_o2.com wrote:
>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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 03 2004 - 02:55:33 CST