ora 02049 distributed_lock_timeout Options

[rokyj055]

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 ) ;

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

[burleson]

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.