ORA-40210 time-out occ…

***Checked for relevance on 14-Aug-2012***

Problem Description
-------------------

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

Error:  ORA 4021
Text:   time-out occurred whilewaiting to lock object
-----------------------------------------------------------------------------
Cause:  While trying to lock a library object, atime-out occurred.
Action: Retry the operation later.

Solution Description
--------------------

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

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

Where is the package name (usually in alluppercase).  If there is a row
returned, then the package is already locked and cannot be droppeduntil the
lock is released.  Returned from the query abovewill be the SID that has this
locked. You can then use this to find out which session hasobtained the lock.

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

  If an ORA-4021 error is not returned and thecommand continues to hang after
issuing the CREATE OR REPLACE or DROP statment, you will need to dofurther
analysis see where the hang is occuring. A starting point is tohave a
look in v$session_wait, see the referenced NOTE.61552.1 for how toanalyze 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:
create or replace procedure lockit as
begin
      null;
end;
/ 

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

create or replace procedure lockit as
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object LOCKIT

Session 3:

connect  / as sysdba
col owner for a10
col object for a15
select * from v$access where object = 'LOCKIT';

Result:
      SIDOWNER     OBJECT         TYPE
---------- ---------- ---------------------------------------
        9 OPS$HNAPELLOCKIT         PROCEDURE   

select sid, event from v$session_wait;

Result:

      SID EVENT
--------------------------------------------------------------------------
        9 null event
...
       12 library cache pin

In the above result, the blocking sid 9 waits for nothing whilesession 12, the
hanging session, is waiting for event library cache pin.

References
----------

Note.169139.1   How to analyzeORA-04021 or ORA-4020 errors?
Note:1071378.6  ORA-04021: ERROR UPONEXECUTING/RECREATING PROCEDURE, NOTHING IN V$ACCESS
NOTE.61552.1   Diagnosing Database Hanging Issues

Additional Search Words
-----------------------

hang CREATE OR REPLACE PACKAGE BODY DROP recreate PL/SQL plsqlPROCEDURE
FUNCTION