Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Thursday, July 15, 2010

Problem with "library cache pin' when deplyoing the package

Problem: Today i have been asked to deploy a package in the production. Same package has been deployed in staging and UAT successfully with in a minute. When I deployed the same package in production it did not go through. after 10 min I got following error.

SQL> @PKGALERTS.pkb;
CREATE OR REPLACE PACKAGE BODY Pkgalerts AS
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

I tried it couple of time but got the same error again. Please help.

Solution:

select sid, event, p1raw, seconds_in_wait,wait_time
from v$session_wait
where event = 'library cache pin'
and state = 'WAITING';

SID,EVENT,P1RAW,SECONDS_IN_WAIT,WAIT_TIME
4962,library cache pin,070000031733BDF8,51,0



SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob WHERE kglhdadr='070000031733BDF8'

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='070000031733BDF8'

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='070000031733BDF8'

and then compile the object.
SQL> @PKGALERTS.pkb

Package body created.

No comments: