I have one procedure which is schedule to run dialy. This procedure
Only fails on Saturday but run successfully other week days. Some times it also run on Saturday but it takes long time to execute, almost 3 hours.
I am geting the following below oracle error when the procedure failed to execute:
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object DATA.T1_Trigger
ORA-06512: at "DATA.MISSING_ITEM", line 34
ORA-06512: at line 1
DATA.MISSING_ITEM is a procedure and DATA.T1_Trigger
is a trigger.
The procedure gets time out when it tries to disable the trigger 'DATA.T1_Trigger' on line 34.
Alter trigger T1_Trigger disable;
If the PL/SQL object being modified is being locked by a library cache pin that is held by another session using a PL/SQL client, you may be encountering this problem. The blocking session should, but does not, release the lock when it is finished modifying the object.
Thanks for the reply!
If i kill the blocking session, i may loose the data. Moreover why such locking happens only on saturday?why not daily?
Any other way i can solve this problem without killing the blocking session.
Well i will suggest my senior DBA for this.
what i understood from your previous mail :
Say their are two sessions:
One session is holding lock on table1
second session i.e. Procedure session which is trying to hold lock on table1 gets time out because locks are not realese by session one.?