From oracle-l-bounce@freelists.org Wed Dec 1 08:25:03 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iB1EP3g11199 for ; Wed, 1 Dec 2004 08:25:03 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iB1EP3x11194 for ; Wed, 1 Dec 2004 08:25:03 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 531A272CD6F; Wed, 1 Dec 2004 09:31:25 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21918-32; Wed, 1 Dec 2004 09:31:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4F9E972CD63; Wed, 1 Dec 2004 09:28:51 -0500 (EST) In-Reply-To: <7765c897041201052868a99b2c@mail.gmail.com> To: oracle-l@freelists.org Subject: Managing Distributed Transactions MIME-Version: 1.0 Message-ID: From: Tony.Adolph@o2.com Date: Wed, 1 Dec 2004 15:22:08 +0100 X-MIMETrack: Serialize by Router on MUC8LX01/SPOKE/o2-Germany(Release 6.5.1|January 21, 2004) at 12/01/2004 15:22:00, Serialize complete at 12/01/2004 15:22:00 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 12952 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Tony.Adolph@o2.com Precedence: normal Reply-To: Tony.Adolph@o2.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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