– session 1
Create or replace procedure dummy is
begin
null;
end;
/
--session 1
Begin
Dummy;
Dbms_lock.sleep(1000);
End;
/
–session 2
alter procedure dummy compile;
–session 3 以下均session 3
select sid, event, p1raw from v$session_wait where event = 'library cache pin';
SID EVENT P1RAW
---------- -------------------------------------------------- ----------------
19 library cache pin 000000006222D828
--19在等待000000006222D828句柄
col KGLNAOWN for a30
col KGLNAOBJ for a40
select kglnaown, kglnaobj from x$kglob where kglhdadr = '000000006222D828';
KGLNAOWN KGLNAOBJ
------------------------------ ----------------------------------------
SYS DUMMY
--目前持有者
select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = 'DUMMY';
KGLHDADR KGLNAOWN KGLNAOBJ
---------------- ------------------------------ ----------------------------------------
000000006222D828 SYS DUMMY
000000006222C0C8 PUBLIC DUMMY
select sid, serial#,s.event, sql_text from dba_kgllock w, v$session s, v$sqlarea a
where w.kgllkuse = s.saddr and w.kgllkhdl='000000006222D828'
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value;
SID SERIAL# EVENT SQL_TEXT
---------- ---------- ------------------------------ ------------------------------------------------------------
21 15126 PL/SQL lock timer Begin Dummy; Dbms_lock.sleep(1000); End;
19 52287 library cache pin alter procedure dummy compile
19 52287 library cache pin alter procedure dummy compile
21 15126 PL/SQL lock timer Begin Dummy; Dbms_lock.sleep(1000); End;
--21在锁定，19在等待
SELECT *
FROM v$session_wait
WHERE event = 'library cache pin'
ORDER BY p1raw;
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr = '000000006222D828';
Owner Object
------------------------------ --------------------
SYS DUMMY
--持有者信息
--查找session
SELECT DECODE (lob.kglobtyp,
0, 'NEXT OBJECT',
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
23, 'DIRECTORY',
24, 'QUEUE',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
32, 'INDEXTYPE',
33, 'OPERATOR',
34, 'TABLE SUBPARTITION',
35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION',
41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT',
46, 'RULE SET',
47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION',
52, 'LOCATION',
55, 'XML SCHEMA',
56, 'JAVA DATA',
57, 'SECURITY PROFILE',
59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED')
object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE pn.KGLPNUSE = ses.saddr
AND pn.KGLPNHDL = lob.KGLHDADR
AND lob.kglhdadr = vsw.p1raw
AND vsw.event = 'library cache pin'
ORDER BY lock_mode_held DESC;
OBJECT_TYPE OBJECT_NAME LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME
------------------------------ ------------------------------ -------------- ------------------- ---------- ---------- --------------------
PROCEDURE DUMMY 2 0 21 15126 SYS
PROCEDURE DUMMY 0 3 19 52287 SYS
--21拿着2号共享锁，19号请求3号排他锁
SELECT DISTINCT DECODE(KGLPNREQ,
0,
'holding_session:' || S.SID,
'waiting_session: ' || S.SID) SID,
S.SERIAL#,
KGLPNMOD "Pin Mode",
KGLPNREQ "ReqPin",
A.SQL_TEXT,
KGLNAOWN "Owner",
KGLNAOBJ "Object"
FROM X$KGLPN P, V$SESSION S, V$SQLAREA A, V$SESSION_WAIT SW, X$KGLOB X
WHERE P.KGLPNUSE = S.SADDR
AND KGLPNHDL = SW.P1RAW
AND KGLHDADR = SW.P1RAW
AND s.EVENT LIKE 'library cache%'
AND (A.HASH_VALUE, A.ADDRESS) IN
(SELECT DECODE(SQL_HASH_VALUE, 0, PREV_HASH_VALUE, SQL_HASH_VALUE),
DECODE(SQL_HASH_VALUE, 0, PREV_SQL_ADDR, SQL_ADDRESS)
FROM V$SESSION S2
WHERE S2.SID = S.SID);
SID SERIAL# Pin Mode ReqPin SQL_TEXT Owner Object
------------------------------ ---------- ---------- ---------- ---------------------------------------- ---------- ------------------------------
waiting_session: 19 52287 0 3 alter procedure dummy compile SYS DUMMY
--等待者是19，用的命令是alter，亲求3号
SELECT DISTINCT /*+ ordered */ W1.SID WAITING_SESSION,
H1.SID HOLDING_SESSION,
W.KGLLKTYPE LOCK_OR_PIN,
OD.TO_OWNER OBJECT_OWNER,
OD.TO_NAME OBJECT_NAME,
OC.TYPE,
DECODE(H.KGLLKMOD,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') MODE_HELD,
DECODE(W.KGLLKREQ,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') MODE_REQUESTED,
XW.KGLNAOBJ WAIT_SQL,
XH.KGLNAOBJ HOLD_SQL
FROM DBA_KGLLOCK W,
DBA_KGLLOCK H,
V$SESSION W1,
V$SESSION H1,
V$OBJECT_DEPENDENCY OD,
V$DB_OBJECT_CACHE OC,
X$KGLLK XW,
X$KGLLK XH
WHERE (((H.KGLLKMOD != 0) AND (H.KGLLKMOD != 1) AND
((H.KGLLKREQ = 0) OR (H.KGLLKREQ = 1))) AND
(((W.KGLLKMOD = 0) OR (W.KGLLKMOD = 1)) AND
((W.KGLLKREQ != 0) AND (W.KGLLKREQ != 1))))
AND W.KGLLKTYPE = H.KGLLKTYPE
AND W.KGLLKHDL = H.KGLLKHDL
AND W.KGLLKUSE = W1.SADDR
AND H.KGLLKUSE = H1.SADDR
AND OD.TO_ADDRESS = W.KGLLKHDL
AND OD.TO_NAME = OC.NAME
AND OD.TO_OWNER = OC.OWNER
AND W1.SID = XW.KGLLKSNM
AND H1.SID = XH.KGLLKSNM
AND (W1.SQL_ADDRESS = XW.KGLHDPAR AND W1.SQL_HASH_VALUE = XW.KGLNAHSH)
AND (H1.SQL_ADDRESS = XH.KGLHDPAR AND H1.SQL_HASH_VALUE = XH.KGLNAHSH);
WAITING_SESSION HOLDING_SESSION LOCK OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQU WAIT_SQL HOLD_SQL
--------------- --------------- ---- -------------------- ------------------------------ -------------------- --------- --------- ---------------------------------------- --------------------
19 21 Pin SYS DUMMY PROCEDURE Share Exclusive alter procedure dummy compile Begin Dummy; Dbms_lo
--等待者是19，持有者是21，请求Exclusive，用命令alert,对象是alter ck.sleep(1000); End;
SELECT DISTINCT SID USING_SID,
S.SERIAL#,
KGLPNMOD “Pin Mode”,
KGLPNREQ “Req Pin”,
kglnaown"Owner",
KGLNAOBJ “using_Object”
FROM XSESSION S, X$KGLOB X
WHERE P.KGLPNUSE = S.SADDR
AND KGLPNHDL = KGLHDADR
AND P.KGLPNUSE = S.SADDR
AND KGLPNREQ = 0
AND UPPER(KGLNAOBJ) = UPPER(’&obj’);
USING_SID SERIAL# Pin Mode Req Pin Owner using_Object
21 15126 2 0 SYS DBMS_LOCK
21 15126 2 0 SYS DUMMY
–编译前检查有没有人持有
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
