RE: Why Library Cache Pin on both sides?

Which release of oracle? What platform?

Ian MacGregor
Stanford Linear Accelerator Center

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Binh Pham Sent: Wednesday, February 07, 2007 11:17 AM To: Mark.Bobak_at_il.proquest.com; oracle-l_at_freelists.org Subject: RE: Why Library Cache Pin on both sides?

Mark,

Thanks for the script. However, the question that I have about why both sides got locked out has not been answered and I'd like to understand why that is the case.

Thanks.         

	From: "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>
	Reply-To: Mark.Bobak_at_il.proquest.com
	To: <binhpham15_at_hotmail.com>,<oracle-l_at_freelists.org>
	Subject: RE: Why Library Cache Pin on both sides?
	Date: Wed, 7 Feb 2007 13:48:40 -0500
	
	
	Hi Binh,
	 
	This is my script for tracking down who is blocking who in the event of a library cache pin event:
	 
	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
	 

	--

	Mark J. Bobak

	Senior Oracle Architect

	ProQuest Information & Learning

	

	There is nothing so useless as doing efficiently that which shouldn't be done at all.  -Peter F. Drucker, 1909-2005

	

	 



________________________________


	From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Binh Pham
	Sent: Wednesday, February 07, 2007 12:35 PM
	To: oracle-l_at_freelists.org
	Subject: Why Library Cache Pin on both sides?
	
	
	Why there are library cache pin on both sides when someone tries to compile a package that are also being used by users and BOTH SIDES ARE FROZEN.
	 
	I thought that if one side is currently using it then only the later one should wait not both.
	 
	Thanks.



________________________________


	Check out all that glitters with the MSN Entertainment Guide to the Academy Awards(r) <http://g.msn.com/8HMAENUS/2755??PS=47575> -- http://www.freelists.org/webpage/oracle-l 
	




________________________________

Talk now to your Hotmail contacts with Windows Live Messenger. <http://g.msn.com/8HMBENUS/2746??PS=47575> -- http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l