Oracle-L: RE: How to find the session holding the library cache pin.

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 28 Aug 2003 12:49:39 -0800
Message-ID: <F001.005CDC99.20030828124939@fatcity.com>

Murali,

Running this should help in identifying the object being locked and the SID holding that pin.

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

column waiter format a15
column holder format a15
column held_object format a47
column lock_or_pin format a15
column address format a15
column mode_requested format a15
set feedback off
set echo off
select /*+ ORDERED */ w1.sid || '/' || w1.username waiter, h1.sid || '/' || h1.username holder,
o.to_owner || '.' || o.to_name held_object, w.kgllktype lock_or_pin,

w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown')
mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown')
mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1, v$object_dependen
cy o
where (((h.kgllkmod != 0)

and (h.kgllkmod != 1)
and ((h.kgllkreq = 0)
 or (h.kgllkreq = 1)))
 and      (((w.kgllkmod = 0)

or (w.kgllkmod= 1))
and ((w.kgllkreq != 0)
and (w.kgllkreq != 1))))

     and  w.kgllktype    =  h.kgllktype
         and  w.kgllkhdl         =  h.kgllkhdl
         and  w.kgllkuse     =   w1.saddr
         and  h.kgllkuse     =   h1.saddr
         and  w.kgllkhdl     =  o.to_address

;

-----Original Message-----
[mailto:Murali_Pavuloori/Claritas_at_claritas.com] Sent: Thursday, August 28, 2003 1:14 PM
To: Multiple recipients of list ORACLE-L

Gurus:

One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state....

I queried the v$session_wait and found that his session is waiting for the library cache pin....question is how to tell which session is holding the enqueue?

Thanks in advance for your help.

Murali.

--

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

Author:
INET: Murali_Pavuloori/Claritas_at_claritas.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

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

Author: John Kanagaraj
INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------