LIBRARY CACHE LOCK WAITS AND NO BLOCKER FOUND
数据库过程无法调试，看了一下等待事件，发现是library cache pin。
--查看等待的的会话
SQL> select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 'library%';
SID SEQ#
EVENT
P1 P1RAW P2 P2RAW P3
P3RAW STATE
---------- ----------
----------------------------------------------------------------
---------- ---------------- ---------- ---------------- ----------
---------------- -------------------
39 647 library
cache pin 5.04403172
07000003587A4D78 5.04403173 070000037DC33240 200 00000000000000C8
WAITING
522 40434 library cache
lock 5.04403173
07000003712ED5F8 5.04403172 070000034E34E590 1301 0000000000000515
WAITING
--等待的对象
SQL> select sw.sid,xb.kglhdadr as addr,xb.kglhdpar as parent_addr ,xb.kglnaown as owner,xb.kglnaobj
2 from x$kglob xb,v$session_wait sw
3 where xb.kglhdadr = sw.p1raw
4 and sw.event like 'library%' ;
SID ADDR PARENT_ADDR OWNER KGLNAOBJ
----------
---------------- ----------------
----------------------------------------------------------------
--------------------------------------------------------------------------------
39 07000003587A4D78
07000003584CF198
begin --sys.dbms_session.reset_package;
sys.dbms_debug.probe_version(major =
522 07000003712ED5F8 07000003712ED5F8 SYS DBMS_DEBUG
--锁定情况
SQL> SELECT a.SID, a.username,
2 case b.kglpnmod when 2 then 'S' when 3 then 'X' end "Mode",
3 case b.kglpnreq when 2 then 'S' when 3 then 'X' end "Req" ,
4 a.program, b.addr, b.kglpnadr, b.kglpnuse,b.kglpnses, b.kglpnhdl, b.kglpnlck
5 FROM v$session a, x$kglpn b
6 WHERE a.saddr = b.kglpnuse
7 --AND b.kglpnmod <> 0
8 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') ;
SID USERNAME
Mode Req
PROGRAM ADDR
KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL
KGLPNLCK
---------- ------------------------------
-------------------------------- --------------------------------
------------------------------------------------ ----------------
---------------- ---------------- ---------------- ----------------
----------------
39
MAIN
S
plsqldev.exe 0000000110381F78
070000037DC33240 070000032B417910 070000032B417910 07000003587A4D78
070000034E4A2300
522 MAIN
X
plsqldev.exe 0000000110381FD0
07000003691C1980 070000032D45E4B0 070000032D45E4B0 07000003587A4D78
070000034E27CEF0
会话39是在等待pin 07000003587A4D78 ，而07000003587A4D78 被会话522以模式X lock住，所以一直等待。
但是522已经执行了几天了，它又在等待07000003712ED5F8（dbms_debug）上加X锁，它又是被谁阻塞了呢？
做了个systemstate trace，但是也找不到holder
[/oracle]$sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jul 27 20:30:05 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.8.0 - Production
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
Session altered.
SQL> ALTER SYSTEM SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
System altered.
开SR后，得到答复是：
|
||||
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10867315/viewspace-670642/ ，如需转载，请注明出处，否则将追究法律责任。
