-- FILE: libcache_lock.sql
--
-- AUTHOR: Andy Rivenes
--
-- DATE: 01/22/2003
--
-- DESCRIPTION:
-- Query to display library cache lock/pin blockers and waiters
-- Source: Note: 122793.1, HOW TO FIND THE SESSION HOLDING
-- A LIBRARY CACHE LOCK
--
-- The address of the object should allow access through
-- v$open_cursor, v$sql views.
--
-- REQUIREMENTS:
-- Access to x$ tables (connect as sys or sysdba).
--
-- MODIFICATIONS:
--
--
SET LINESIZE 132;
SET PAGESIZE 60;
SET TRIMSPOOL off;
--
COLUMN sid HEADING 'SID' FORMAT 9999;
COLUMN objtyp HEADING 'Object|Type' FORMAT A25;
COLUMN lktyp HEADING 'Lock|Type' FORMAT A4;
COLUMN lkmod HEADING 'Mode|Held' FORMAT A10;
COLUMN lkreq HEADING 'Mode|Request' FORMAT A10;
COLUMN objaddr HEADING 'Address' FORMAT A10;
COLUMN objdef HEADING 'Object' FORMAT A30 WORD_WRAPPED;
--
BREAK ON lk.kgllkhdl SKIP 1;
--
SELECT s.sid,
DECODE(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body',
3, 'trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source',
14, 'Java Resource', 32, 'Java Data', TO_CHAR(ob.kglhdnsp)) objtyp,
lk.kgllktype lktyp,
DECODE(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
TO_CHAR(lk.kgllkmod)) lkmod,
DECODE(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
TO_CHAR(lk.kgllkreq)) lkreq,
RAWTOHEX(lk.kgllkhdl) objaddr,
DECODE(ob.kglnaown, NULL, '', ob.kglnaown || '.') || ob.kglnaobj ||
DECODE(ob.kglnadlk, NULL, '', '@' || ob.kglnadlk) objdef
FROM v$session s,
x$kglob ob,
-- dba_kgllock lk
( SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype FROM x$kgllk
UNION ALL
SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype FROM x$kglpn ) lk
WHERE lk.kgllkhdl = ob.kglhdadr
AND lk.kgllkuse = s.saddr
AND lk.kgllkhdl IN ( SELECT DISTINCT kgllkhdl
FROM ( SELECT kgllkhdl, kgllkreq FROM x$kgllk
UNION ALL
SELECT kglpnhdl, kglpnreq FROM x$kglpn )
WHERE kgllkreq > 0 )
ORDER BY lk.kgllkhdl,
lk.kgllkreq ASC,
lk.kgllkmod DESC
/
Andy Rivenes [EMAIL PROTECTED]
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andy Rivenes INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).