--  FILE:   libcache_lock.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (c) 2003-2004, AppsDBA Consulting.  All Rights Reserved.
--  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:
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
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
/