Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ora 02049 distributed_lock_timeout
rokyj055
post Mar 20 2012, 01:02 AM
Post #1


Advanced Member
***

Group: Members
Posts: 75
Joined: 23-January 12
Member No.: 46,637



Dea All ,

I got this error on my production database , please help me

----------------------------------------------------------

sga_target = 11040 mb

errot : ora 02049 distributed_lock_timeout

i have db_links in database.

i fired this query
--------------------------------------------------------------------------------------------------------
UPDATE NFS90401_ods c SET TRansDATE = (SELECT
(CASE WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),3) = '222'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),3) = '333')
THEN A.postdate +4 WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),2) = '22'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),2) = '33')
THEN A.postdate +3 WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),1) = '2'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),1) = '3')
THEN A.postdate +2 ELSE A.POSTDATE +1 END) trdate
from NFS90401_ods a, D001013_VW B where a.lbrcode = b.lbrcode

AND a.POSTDATE = c.postdate AND a.LBRCODE = c.lbrcode
and to_number(substr(to_char(A.postdate , 'dd/mm/yyyy'),4,2))= b.calmonth

and to_number(substr(to_char(A.postdate , 'dd/mm/yyyy'),7,4))= b.calyear

AND C.LBRCODE between 41 AND 50
GROUP by
(CASE WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),3) = '222'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),3) = '333')
THEN A.postdate +4 WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),2) = '22'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),2) = '33')
THEN A.postdate +3 WHEN (SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),1) = '2'
OR SUBSTR(B.HOLIDAYs,to_number(substr(to_char(A.postdate +1, 'dd/mm/yyyy'),1,2)),1) = '3')
THEN A.postdate +2 ELSE A.POSTDATE +1 END), a.lbrcode ) ;

--------------------------------------------------------------------------------
Go to the top of the page
 
+Quote Post
burleson
post Mar 20 2012, 05:06 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,620
Joined: 26-January 04
Member No.: 13



Hi,

>> UPDATE NFS90401_ods c SET TRansDATE

This is causing a deadlock.

If you re-run it during a less busy time, it should run fine . . .

The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS. If you want to reduce the network overhead associated with keeping the link open, then use this clause to close the link explicitly if you do not plan to use it again in your session.

http://www.dba-oracle.com/sf_ora_02049_tim...ng_for_lock.htm

See MOSC note: 1018919.102 and look at adjusting your distributed_lock_timeout value and distributed_recovery_connection_hold_time.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th October 2014 - 06:58 AM