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

最新推荐文章于 2022-05-17 11:26:21 发布

citan9333

最新推荐文章于 2022-05-17 11:26:21 发布

阅读量126

点赞数

How to Find which Session is Holding a Particular Library Cache Lock
如何找到会话持有的锁为Library Cache Lock
今天在生产上开发说一个view删除不了，我试了一下，也不行。无思路，光辉说看看等待事件。
select w.SID,
w.EVENT,
w.STATE,
round(w.SECONDS_IN_WAIT / 60 / 60, 2) hours,
s.USERNAME,
s.COMMAND,
s.BLOCKING_SESSION
from v$session_wait w, v$session s
where s.SID = w.SID
and w.STATE = \'WAITING\'
and w.wait_class <> \'Idle\'
order by w.SECONDS_IN_WAIT desc
发现有个SID为2039的 Library Cache Lock 的等待事件，查看MOS，MOS上说有两种方法可以查看。
1. EXAMINE THE X$KGLLK TABLE
X$KGLLK表只能被SYS/INTERNAL用户访问
通过关联v$sql去查sql_text时查不到东西。
-查看等待事件为librarycache lock的session 的session address (SADDR):
SQL> select sid,saddr from v$session where event=\'library cache lock\';
SID SADDR
---------- ----------------
2039 000000069C6D09A8
SQL> select kgllkhdl Handle, kgllkreq Request,kglnaobj Object from x$kgllk where kgllkses = \'000000069C6D09A8\' and kgllkreq > 0;
HANDLE REQUEST OBJECT
---------------- ---------- --------------------------------
00000004109DDDE0 3 MV_F_JIESUAN_VIEW
/*我要删除的正是这个view*/
--查看该回话的连接程序和用户名：
select sid,username,terminal,program from v$session where saddr = \'000000069C6D09A8\'；
--或者直接通过回话的ADDR查到用户名，终端，程序
select sid, username, terminal, program
from v$session
where saddr in
(select kgllkses
from x$kgllk lock_a
where kgllkreq > 0
and exists (select lock_b.kgllkhdl
from x$kgllk lock_b
where kgllkses = \'572eac94\' /* blocking session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq = 0));
--根据SID，去查到serial#，然后kill掉：
select sid,serial# from v$session where sid=\'XXX\' /*sid number*/
alter system kill session \'SID,serial#\' immediate;
二、另外一种方法是通过oradebug做dump这里不再赘述
参考MOS ID:122793.1