You can try this script and see if it works for your situation. We had an
elusive problem like this that we were able to gain more information about
using this query:
- 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).
Received on Mon Jul 07 2003 - 14:31:46 CDT
