ORA-04021: timeout occurred while waiting to lock object – Library Cache Pin

Few of the times during deployment users complain of their session almost hanged and that is when is start digging. Recently i was pinged by a user where she was trying to run .sql script which was doing create or replace procedure and she got the error “ORA-04021: timeout occurred while waiting to lock object”.I asked her to run the sql again and her session showed “Library Cache Pin” wait event.

Library cache pins are used to manage library cache concurrency.PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.

So, how to diagnose it –

Identify the session

select sid, event, p1raw from v$session_wait where event = 'library cache pin';

Identify the object that is being waited for

 SELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr='value of p1raw'

Find the session pining the object

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status, kglpnmod "Mode", kglpnreq "Req" FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND  kglpnhdl='value of p1raw'


select sid, serial#, sql_text from dba_kgllock w, v$session s, v$sqlarea a where w.kgllkuse = s.saddr and w.kgllkhdl='value of p1raw' and s.sql_address = a.address and s.sql_hash_value = a.hash_value;

The “Mode” and “Request” can be either exclusive (3) or shared (2). Try an check with the application owners/users if the session holding the pin can be killed. For m, i was was lucky enough to kill the holding session.

One thought on “ORA-04021: timeout occurred while waiting to lock object – Library Cache Pin

  1. Thanks for the post. We had similar error ORA-04021: timeout occurred while applying a patch and resolved by restarting the DB.

    similar details i found here for my ORA-04021 error. here

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