RE: library cache lock

From: David Sharples <dsharples_at_cerebrussolutions.com>
Date: Wed, 2 Jun 2004 15:08:14 +0100
Message-ID: <EA29A3FCC723674293FD6286D3F0513E77BD50@louis.cerebrus.com>

Thanks very much - all I need to do now is go get sys access from the dba, all I have is dba rights at present  

Dave  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: 02 June 2004 14:58
To: oracle-l_at_freelists.org
Subject: RE: library cache lock  

David,  

Try the following query to find the blocked sessions and which session is the blocker.

Note that due to the order by, the first session should be the blocker.  

-Mark  

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

order by pn.kglpnmod desc, pn.kglpnreq desc /

	-----Original Message-----
	From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Sharples

	Sent: Wednesday, June 02, 2004 9:40 AM
	To: oracle-l_at_freelists.org
	Subject: RE: library cache lock

	I searched (probably on wrong words) didn't return anything

useful as I could see, can you show me a url?                   

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khedr, Waleed

	Sent: 02 June 2004 14:10
	To: oracle-l_at_freelists.org
	Subject: RE: library cache lock

	 

	The metalink has answers for similar issues.

	 

		-----Original Message-----
		From: David Sharples


[mailto:dsharples_at_cerebrussolutions.com]

		Sent: Wednesday, June 02, 2004 8:44 AM
		To: oracle-l_at_freelists.org
		Subject: library cache lock

		Hi, got a strange problem.  I am trying to truncate a

partition in a table, but it just hangs forever, it is waiting on a library cache lock. Anyone know why and how I can clear it - flushing the shared pool didn't help and there are no other sessions touching that table.

                Thanks

                Dave

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jun 02 2004 - 09:02:32 CDT