RE: library cache lock on import

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 25 Jan 2006 15:00:34 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF270685DBA1@AABO-EXCHANGE02.bos.il.pqe>

Juan,

Here's a script I call liblock.sql that I use when I see library cache lock waits, to see who is blocking who. Hope it helps....

Here ya go:
 select /*+ ordered use_nl(lob pn ses) */

       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 v$session_wait vsw,
       x$kglob lob,
       x$kglpn pn,
       v$session ses
 where vsw.event = 'library cache lock'

   and vsw.p1raw = lob.kglhdadr
   and lob.kglhdadr = pn.kglpnhdl
   and pn.kglpnmod != 0
   and pn.kglpnuse = ses.saddr
/

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"There are 10 types of people in the world: Those who understand binary, and those who don't."

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Wednesday, January 25, 2006 2:21 PM To: oracle-l_at_freelists.org
Subject: library cache lock on import

Hi please if you can,
I'm getting an library cache lock on an import, it seems to be related to the compilation of some procedures using database links, directed to the same database (this is because in others systems the same links points to other database, in this case not)

Thep roblem is I can-t figure out what means this exactly for example chyecking v$session row_wait_obj#, I found the table SCHEDULER$_SRCQ_INFO, that don't makes too much sense to me

or the namespace I can't find on V$DB_OBJECT_CACHE

Thanks
SELECT * FROM V$SESSION_WAIT
where not wait_class='Idle'
order by 1,2

SID                                    SEQ#                           
       EVENT                                                          
 P1TEXT                                                           P1  
                                  P1RAW      P2TEXT                   
                                       P2                             
       P2RAW      P3TEXT                                              
            P3                                     P3RAW     
WAIT_CLASS_ID                          WAIT_CLASS#                    
       WAIT_CLASS                                                     
 WAIT_TIME                              SECONDS_IN_WAIT               
        STATE


-------------------------------------- -------------------

91                                    	457                            
      	library cache lock                                             
	handle address                                                 
	858746552                             	332F6EB8  	lock
address       
                                            	872673408

            	3403F080  	100*mode+namespace

                 	201                                   	000000C9

	3875070507                            	4

       	Concurrency                                                   
 	0                                     	1220

        	WAITING

--

Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i

8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 25 2006 - 14:00:34 CST