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

From: Diego Cutrone <diegocutrone_at_yahoo.com.ar>
Date: Thu, 28 Aug 2003 13:54:33 -0800
Message-ID: <F001.005CDCD0.20030828135433@fatcity.com>

Murali:

After identifying a LC pin wait in v$session_wait, use dba_lock_internal (it shows DDL/DML Locks, LC Locks, Latch Locks, etc)

select to_char(SESSION_ID,'999') sid ,

substr(LOCK_TYPE,1,30) Type, 
substr(lock_id1,1,23) Object_Name, 
substr(mode_held,1,4) HELD, substr(mode_requested,1,4)

REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like 'library cache pin%') ;

You can also join
v$session_wait.p1raw to x$kglpn.KGLPNHDL and v$session.saddr to x$kglpn.kglpnuse to get the sid of the session that's holding the pin.

HTH
Greetings
Diego Cutrone

>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.

---

Internet GRATIS es Yahoo! Conexión
4004-1010 desde Buenos Aires. Usuario: yahoo; contraseña: yahoo Más ciudades: http://conexion.yahoo.com.ar

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Diego=20Cutrone?=
  INET: diegocutrone_at_yahoo.com.ar

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).

Received on Thu Aug 28 2003 - 16:54:33 CDT