Re: DBA_HIST_ACTIVE_SESS_HISTORY library cache pin waits

From: Riyaj Shamsudeen <>
Date: Wed, 26 Feb 2014 16:26:53 -0800
Message-ID: <>

Library cache pins are held in share mode (kglpnmod=2) during package execution and pins are requested in exclusive mode pglpnreq=3) for package (re)creation. So, unless I missed a new feature recently, you can't create a package while the package is still being executed in another session.

Here is small test case:

Copy the contents to a file , say pkg1.sql -----cut---
create or replace package pkg1 as
  procedure proc1( p_sleep in number);

create or replace package body pkg1 as
  procedure proc1 (p_sleep in number) is     begin

--- cut----
My test case uses sqlplus.

  1. From session #1, create the above package: _at_pkg1.sql
  2. From session #2, execute the package as exec pkg1.proc1(300); -- this should sleep for 5 minutes.
  3. revert to session #1 and try recreating the package _at_pkg1.sql. This should hang ( ctrl+c after a minute, logoff, and should hang.)
  4. Open a new session, and execute the following to see the library pin contention. Require sys access (I know, I know, I need to update the script to use 11g new view).

select ses.sid, ses.serial#, ses.username, ses.program, ses.module, ses.machine, pin.* from x$kglpn pin, v$session ses where kglpnhdl in
select kglpnhdl from x$kglpn
where kglpnreq >0
and pin.kglpnses=ses.saddr

I am really not sure, how the OP was able to recreate the package while the package is in use? I tested this in (RAC & exadata if that make any difference). Is the problem more deeper than single package issue  (meaning is there a dependency issue we are missing?)

  What version of RDBMS and platform? Are you sure the original package was created immediately?


Riyaj Shamsudeen
Principal DBA,
Ora!nternals - - Specialists in Performance, RAC and EBS
Blog: Oracle ACE Director and OakTable member <>

Co-author of the books: Expert Oracle
Practices<> , Pro Oracle SQL, <> <>Expert
RAC Practices 12c. <> Expert PL/SQL practices <>

Received on Thu Feb 27 2014 - 01:26:53 CET

Original text of this message