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
