ORA 4021 - time-out occurred while waiting to lock object

Problem Description

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

Trying to recreate a package, procedure or function (CREATE OR REPLACE ...) or dropping a the same (DROP PACKAGE ...) causes the application to hang (ie: SQL*Plus hangs after submitting the statement). Eventually ORA-4021 errors occur after the timeout (usually 5 minutes).

Solution Description

Verify that the package is not locked by another user by selecting from V$ACCESS view.  To do this, run:

SELECT * FROM v$access WHERE object = '';

Where is the package name (usually in all uppercase).  If there is a row returned, then the package is already locked and cannot be dropped until the lock is released.  Returned from  he query above will be the SID that has this locked. You can then use this to find out which session has obtained the lock.

In some cases, that session might have been killed and will not show up.  If this happens, the lock will not be release immediately.  Waiting for PMON to clean up the lock might take some time. The fastest way to clean up the lock is to recycle the database instance.

If an ORA-4021 error is not returned and the command continues to hang after issuing the CREATE OR REPLACE or DROP statment, you will need to do further analysis see where the hang is occuring. A starting point is to have a look in v$session_wait, see the referenced Note 61552.1 for how to analyze hang situations in general

Solution Explanation

Consider the following example:

Session 1:

create or replace procedure lockit(secs in number) as

shuttime date;

begin

shuttime := sysdate + secs/(24*60*60);

while sysdate <= shuttime loop

null;

end loop;

end;

/

show err

begin

-- wait 10 minutes

lockit(600);

end;

/

Session 2:

SQL> create or replace procedure ops$hnapel.lockit as

2  begin

3     null;

4  end;

5  /

Result: hang and eventually (the timeout is 5 minutes):

create or replace procedure ops$hnapel.lockit as

*

ERROR at line 1:

ORA-04021: timeout occurred while waiting to lock object OPS$HNAPEL.LOCKIT

Session 3:

SQL> connect  / as sysdba

Connected.

SQL> col owner for a10

SQL> col object for a15

SQL> select * from v$access where object = 'LOCKIT';

SID OWNER      OBJECT          TYPE

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

9 OPS$HNAPEL LOCKIT          PROCEDURE

SQL> select sid, event from v$session_wait;

SID EVENT

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

9 null event

...

12 library cache pin

As you can see, the blocking sid 9 waits for nothing while session 2, the hanging session, is waiting for event library cache pin.

Source : Oracle Metalink