Home » SQL & PL/SQL » SQL & PL/SQL » My Procedure: lock held by in-doubt distributed transaction 11.11.2720358
My Procedure: lock held by in-doubt distributed transaction 11.11.2720358 [message #282095] Tue, 20 November 2007 20:01 Go to next message
pengamen2007
Messages: 3
Registered: November 2007
Junior Member
Dear all,
i try to execute my procedure in command prompt, but unfortunatelly it doesn't work properly, i find out the error report is:

*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 11.11.2720358
ORA-02063: preceding line from DWH
ORA-06512: at "ERIKSON.PRC_KLU10JT_NOJOIN", line 18
ORA-06512: at line 1

and this is my procedure script:


CREATE OR REPLACE PROCEDURE ERIKSON.prc_klu10jt_nojoin
IS
cklu NUMBER (7);
cjumlah NUMBER (18);

CURSOR p1
IS
SELECT b.mfklu cklu, SUM (a.ptjmby) cjumlah
FROM dat_ssp_mp3@dwh a, mfwp@dwh b
WHERE a.kp_kd = b.mfkpp
AND SUBSTR (a.mfkap, 1, 3) = b.mfcab
AND a.mfnpwp = b.mfnpwp
AND SUBSTR (a.mfnpwp, 1, 2) IN
('17', '18', '19', '27', '28', '29', '37', '38', '39')
AND a.thnby = '2005'
GROUP BY b.mfklu;
BEGIN
FOR a IN p1
LOOP
UPDATE klu_mp3_05_nojoin
SET jumlah = a.cjumlah
WHERE tkdklu = a.cklu;

COMMIT;
END LOOP;
COMMIT;
END;
/

when i try to search this error in one of search engine, it told me that there was a locking process againsted the procedure to run??so what should i do?should i told my DBA?
Re: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358 [message #282096 is a reply to message #282095] Tue, 20 November 2007 20:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Remove the COMMIT from within the LOOP.

If you think you need that commit, search on ORA-01555 to find out why you shouldn't.

If after that you STILL think you need the COMMIT, then load the rows from the remote database that you need into a Global Temporary Table or a PL/SQL Nested Table first, THEN perform your committing loop.

Ross Leishman
Re: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358 [message #282103 is a reply to message #282095] Tue, 20 November 2007 21:13 Go to previous messageGo to next message
pengamen2007
Messages: 3
Registered: November 2007
Junior Member
I run my procedure still in command prompt but without COMMIT within its LOOP, but it didn't work... and about this explanation:
'If you think you need that commit, search on ORA-01555 to find out why you shouldn't.

If after that you STILL think you need the COMMIT, then load the rows from the remote database that you need into a Global

Temporary Table or a PL/SQL Nested Table first, THEN perform your committing loop.'


could you be more exact,please..

thanks..
Re: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358 [message #282104 is a reply to message #282095] Tue, 20 November 2007 21:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>but it didn't work...
My car didn't work.
Tell me how to fix it.

Could you be any more vague & unhelpful?

Please use CUT & PASTE to show us exactly what you are doing & how Oracle is responding.
Re: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358 [message #282156 is a reply to message #282104] Wed, 21 November 2007 00:47 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
http://www.google.com.au/search?hl=en&q=ora-01555&meta=

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.

[Updated on: Wed, 21 November 2007 00:48]

Report message to a moderator

Previous Topic: want to update a column of complex table
Next Topic: query
Goto Forum:
  


Current Time: Sat Dec 03 08:12:12 CST 2016

Total time taken to generate the page: 0.10447 seconds