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

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.

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

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

It looks like NV_Kum is correct. Give me little time and then I will accept your answer. Thanks
Vimal

i said pretty much the same thing and even told you which v$ view to find that info but ok.

regards,
daniels

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

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

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

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