Solved

..i_err_code = ORA-02049: timeout: distributed transaction waiting for lock

Posted on 2006-11-28
10
2,932 Views
Last Modified: 2008-01-09
Experts,

When I try to run one of the package it threw following error. I would like to know the reason and solution.

..i_err_code = ORA-02049: timeout: distributed transaction waiting for lock

Thank you
Vimal
0
Comment
Question by:vish3210
10 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 18034822
Maybe this will be a little help for you:

ORA-02049: timeout: distributed transaction waiting for lock
Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.
Action: treat as a deadlock

(http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_txnman.htm):
Transaction Timeouts
A DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. If these locks continue to block the requesting SQL statement, then the following sequence of events occurs:

A timeout occurs.

The database rolls back the statement.

The database returns this error message to the user:

ORA-02049: time-out: distributed transaction waiting for lock

Because the transaction did not modify data, no actions are necessary as a result of the timeout. Applications should proceed as if a deadlock has been encountered. The user who executed the statement can try to reexecute the statement later. If the lock persists, then the user should contact an administrator to report the problem.

0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18034827
would also help to know what package you are calling, and which database version you are using. this error often occurs when distributed_lock_timeout is set too low and/or if a particular object is locked for update when you are attempting to access it. if you can, look in V$LOCKED_OBJECT for any objects you are trying to access via the package you're calling.

good luck,
daniels
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18038346
just check/verify whether there is any other program which is trying to update/delete the existing records which are modified by some other program but not yet commited/rolledback.

Is this issue seen everytime you call your package.procedure or package.function or only during a certain time saying only between night 10 to 11 PM. If the last is the case, then there could be someother program running during that time which will be the cause of this issue.

thanks
0
 

Author Comment

by:vish3210
ID: 18041051
It looks like NV_Kum is correct. Give me little time and then I will accept your answer. Thanks
Vimal
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18041065
i said pretty much the same thing and even told you which v$ view to find that info but ok.

regards,
daniels
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:vish3210
ID: 18057040
Daniels,

What do you understand by this. I ran this

SQL> select * from V$LOCKED_OBJECT

XIDUSN      XIDSLOT      XIDSQN      OBJECT_ID      SESSION_ID      ORACLE_USERNAME      OS_USER_NAME      PROCESS      LOCKED_MODE
1      46      157      10019      302      OPS$NSCDPRAP      nscdprap      27309      3
1      46      157      9985      302      OPS$NSCDPRAP      nscdprap      27309      3
1      46      157      9984      302      OPS$NSCDPRAP      nscdprap      27309      3


Please tell me what is wrong and how to get out with it. Pleaase

Thanks
Vimal
0
 
LVL 7

Expert Comment

by:Daniel Stanley
ID: 18057135
--find out which objects are locked
select owner, object_name
from dba_objects
where object_id in
(select object_id from V$locked_object);

--identify hash value for session_id in v$locked_object
select sid, PREV_HASH_VALUE
from v$session
where sid=302;


--use hash value from previous query in this
--statement to find out which statement has the object locked.
select sql_text
from v$sqlarea
where hash_value = <PREV_HASH_VALUE>;


good luck, let me know if i can help further.

daniels
0
 

Author Comment

by:vish3210
ID: 18057268
Great Danial,

I've ran that query I got where it has been locked.

select sql_text
from v$sqlarea
where hash_value = 1872360291;

Answer for above query:

SELECT CSCYM.BLNK_STY_DSP_NBR FROM CDB_STY_CYC CSCYM WHERE CSCYM.STY_ID = :B2 AND CSCYM.CYC_ID = :B1

Now what??? and how should I get away with that. Today is last day for this. I need to wrap this up. Thanks again. Please help.

--Vimal


0
 
LVL 7

Accepted Solution

by:
Daniel Stanley earned 125 total points
ID: 18057512
you could just kill that session.  do you know who the OPS$NSCDPRAP/nscdprap user is?  that is who has the object locked..  it may be that the terminal is vacant and that object will be locked until that user logs off.  you could kill his session if you feel you will not interrupt anyone else's work.  

--sid should still be 302 as is above, you'll  need to query v$session to get the serial#
select sid, serial# from v$session where osuser = 'nscdprap';
or
select serial#, osuer from v$session where sid=302;


alter system kill session '302, serial#';


let me know if i can help further.

good luck,
daniels
0
 

Author Comment

by:vish3210
ID: 18057831
Daniel,
I killed the session and now again I started running my package in OPS$NSCDPRAP schema. It running right now. Let's seeeeeeee. I will mail you tommoroow, the reason being this is one of the package running more than 8 hours sometime in the development schema. If this runs success I will let you by then.

See you tommorrow. Have a great weekend

--Vimal
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now