How to Find which Session is Holding a Particular Library Cache Lock

1. How to Find which Session is Holding a Particular Library Cache Lock
   
2. 
   
3. 如何找到会话持有的锁为Library Cache Lock
   
4. 
   
5. 今天在生产上开发说一个view删除不了，我试了一下，也不行。无思路，光辉说看看等待事件。
   
6. 
   
7. select w.SID,
   
8.        w.EVENT,
   
9.        w.STATE,
   
10.        round(w.SECONDS_IN_WAIT / 60 / 60, 2) hours,
    
11.        s.USERNAME,
    
12.        s.COMMAND,
    
13.        s.BLOCKING_SESSION
    
14.   from v$session_wait w, v$session s
    
15.  where s.SID = w.SID
    
16.    and w.STATE = \'WAITING\'
    
17.    and w.wait_class <> \'Idle\'
    
18.  order by w.SECONDS_IN_WAIT desc
    
19.  
    
20. 发现有个SID为2039的 Library Cache Lock 的等待事件，查看MOS，MOS上说有两种方法可以查看。
    
21. 
    
22. 1. EXAMINE THE X$KGLLK TABLE
    
23. 
    
24. X$KGLLK表只能被SYS/INTERNAL用户访问
    
25. 
    
26. 通过关联v$sql去查sql_text时查不到东西。
    
27. 
    
28. 
    
29. -查看等待事件为librarycache lock的session 的session address (SADDR):
    
30. 
    
31. SQL> select sid,saddr from v$session where event=\'library cache lock\';
    
32. 
    
33.        SID SADDR
    
34. ---------- ----------------
    
35.       2039 000000069C6D09A8
    
36. 
    
37. 
    
38. 
    
39. SQL> select kgllkhdl Handle, kgllkreq Request,kglnaobj Object from x$kgllk where kgllkses = \'000000069C6D09A8\' and kgllkreq > 0;
    
40. 
    
41. HANDLE REQUEST                             OBJECT
    
42. ---------------- ---------- --------------------------------
    
43. 00000004109DDDE0 3 MV_F_JIESUAN_VIEW
    
44. 
    
45. 
    
46. /*我要删除的正是这个view*/
    
47. 
    
48. --查看该回话的连接程序和用户名：
    
49. select sid,username,terminal,program from v$session where saddr = \'000000069C6D09A8\'；
    
50. 
    
51. 
    
52. --或者直接通过回话的ADDR查到用户名，终端，程序
    
53. 
    
54. select sid, username, terminal, program
    
55.   from v$session
    
56.  where saddr in
    
57.        (select kgllkses
    
58.           from x$kgllk lock_a
    
59.          where kgllkreq > 0
    
60.            and exists (select lock_b.kgllkhdl
    
61.                   from x$kgllk lock_b
    
62.                  where kgllkses = \'572eac94\' /* blocking session */
    
63.                    and lock_a.kgllkhdl = lock_b.kgllkhdl
    
64.                    and kgllkreq = 0));
    
65.                    
    
66. --根据SID，去查到serial#，然后kill掉：
    
67. 
    
68. select sid,serial# from v$session where sid=\'XXX\' /*sid number*/
    
69. 
    
70. alter system kill session \'SID,serial#\' immediate;
    
71. 
    
72. 
    
73. 二、另外一种方法是通过oradebug做dump这里不再赘述
    
74. 
    
75. 
    
76. 
    
77. 
    
78. 参考MOS ID:122793.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29210156/viewspace-1310540/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29210156/viewspace-1310540/