Reproduce the library cache lock wait event under Oracle 11g

As you can see from the following example, the recompilation of objects in the production database causes the library cache lock, so you should try to avoid compiling objects during peak business hours. If there is a complex dependency in the package or process, it is very easy to cause the library cache lock to appear, so in the process of application development, should also pay attention to this aspect of the problem.

Session1:

Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

sql> Create or replace procedure pining

Is
Begin
Null
End
/
sql> Create or replace procedure calling
Is
Begin
pining;
Dbms_lock.sleep (200);
End

/

Session2:

Alter procedure pining compile;

Session3:

drop procedure pining;

Session4:

sql> Select Sid, Event,wait_class, seconds_in_wait
2 from V$session_wait W
3 where W.wait_class <> ' Idle ';
SID EVENT Wait_class
---------- ---------------------------------------------------------------- ----------------
6 Library Cache Lock Concurrency
Library Cache Pin Concurrency
191 sql*net message to client Network