DBA Sensation

May 25, 2010

Can’t compile a stored procedure when it’s locked

Filed under: 1. Oracle, [PL/SQL dev&tuning] — Tags: , , — zhefeng @ 10:25 am

Trying to recompile a procedure causes the application to hang
(ie: SQL*Plus hangs after submitting the statement). Eventually ORA-4021 errors
occur after the timeout (usually 5 minutes). Here is the soluation from metalink:
Note:ID 107756.1

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

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 the 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:
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:
SID OWNER OBJECT TYPE
———- ———- ————— ————————
9 OPS$HNAPEL LOCKIT 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 while session 12, the
hanging session, is waiting for event library cache pin.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: