Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Managing Distributed Transactions

Re: Managing Distributed Transactions

From: Kresimir Fabijanic <kfabijanic_at_optusnet.com.au>
Date: Fri, 03 Dec 2004 19:58:13 +1100
Message-ID: <41B02AA5.2000806@optusnet.com.au>


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-l
Received on Fri Dec 03 2004 - 02:55:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US