When the library cache pin wait happens, run this script to see who's blocking you: select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 59, 'RULE', 62, 'EVALUATION CONTEXT', 'UNDEFINED') object_type, lob.KGLNAOBJ object_name, pn.KGLpnMOD lock_mode_held, pn.KGLpnREQ lock_mode_requested, ses.sid, ses.serial#, ses.username FROM x$kglpn pn, v$session ses, x$kglob lob, v$session_wait vsw WHERE pn.KGLpnUSE = ses.saddr and pn.KGLpnHDL = lob.KGLHDADR and lob.kglhdadr = vsw.p1raw and vsw.event = 'library cache pin' order by lock_mode_held desc / Hope that helps, -Mark ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bort, Guillermo [guillermo.bort@xxxxxxx] Sent: Saturday, October 04, 2008 9:43 PM To: oracledbaquestions@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: RE: library cache pin wait Does this happen if you run the same procedures again? Does any procedure contain any java code or call to any java program? If I recall admin workshop II correctly there is a way to explicitly pin an object to the LC, are you certain the code does not do this? Does this happen only the first time you run the procedures or package after compilation? Have you run utlrp after compilation (in case there are any objects invalidated because of the recompilation). Regards. Guillermo Alan Bort EDS - ITO DBA Main Group Arias 1851 Ciudad Autónoma de Buenos Aires (C1429DXC) Argentina Tel: +54 11 4704 3132 Tel: +54 11 4704 3000 E-mail: guillermo.bort@xxxxxxx E-mail: ardbagroup@xxxxxxx E-mail: ardbamain@xxxxxxx From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dba DBA Sent: Friday, October 03, 2008 10:50 PM To: oracle-l@xxxxxxxxxxxxx Subject: library cache pin wait I recompiled a package. It compiled successfully. The package has several procedures. Each has 1 large insert select. The all select from the same table, but insert to different tables. This has happened to me before. 1. I run the package with 1 procedure from 1 session. 2. i try to run the same package with a different procedure in another sessions. The second procedure waits with a library cache wait until the first sessions completes and exits. (I am nohupping this, so I dont know if the exit is required, it is in the script). any ideas? the code is NOT being recompiled. When I look at dba_blockers, I don't see any blocking lockers. i am looking at the wait on the second session. I know the package is not running by. 1. I have dbms_application_info calls inside the package and I do not see these in v$session 2. I check v$sess_io and no IO is incrementing for this session. -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l