ORACLE阻塞

ORACLE阻塞
案例一：

34.//SYS窗口
35.
36.SQL> select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid
37.||','||d.serial# block_msg, a.block
38. 2 from v$lock a,v$lock b,v$session c,v$session d
39. 3 where a.id1=b.id1
40. 4 and a.id2=b.id2
41. 5 and a.block>0
42. 6 and a.sid <>b.sid
43. 7 and a.sid=c.sid
44. 8 and b.sid=d.SID
45. 9 ;
46.
47.BLOCK_MSG BLOCK
48.---------------------------------------- ----------
49.HWANG ('138,305') is blocking 153,15 1
50.
51.SQL> select sid,serial#,username from v$session where username is not null;
52.
53. SID SERIAL# USERNAME
54.---------- ---------- ------------------------------
55. 136 179 SYS
56. 138 305 SCOTT
57. 153 15 SCOTT
58.
59.//这个时候可以杀掉那个BLOCKER
60.
61.SQL> alter system kill session'138,305';
62.
63.System altered.
64.
65.//SCOTT窗口1
66.
67.SQL> select * from t2;
68.select * from t2
69.*
70.ERROR at line 1:
71.ORA-00028: your session has been killed
用到的语句：
select c.terminal || ' (''' || a.sid || ',' || c.serial# ||
''') is blocking ' || b.sid || ',' || d.serial# block_msg,
a.block
from v$lock a, v$lock b, v$session c, v$session d
where a.id1 = b.id1
and a.id2 = b.id2
and a.block > 0
and a.sid <> b.sid
and a.sid = c.sid
and b.sid = d.SID；

select sid,serial#,username from v$session where username is not null;

案例2：
oracle锁阻塞的会话
CREATE OR REPLACE VIEW SYS.V_LOCK AS

[color=red]SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;[/color]
案例3：

查询Oracle中的阻塞锁(以及阻塞在哪个数据上)的SQL

Select '节点 ' || a.INST_ID || ' session ' || a.sid || ',' || a_s.SERIAL# ||
' 阻塞了 节点 ' || b.INST_ID || ' session ' || b.SID || ',' || b_s.SERIAL# blockinfo,
a.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
a.type lock_type,
a.id1,
a.id2,
decode(a.lmode,0,'none',1,null,2,'row-S (SS)',3,'row-X (SX)',4,'share (S)',5,'S/Row-X (SSX)',6,'exclusive (X)') lock_mode,
'后为被阻塞信息' ,
b.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b.TYPE blocked_lock_type,
decode(b.request,0,'none',1,null,2,'row-S (SS)',3,'row-X (SX)',4,'share (S)',5,'S/Row-X (SSX)',6,'exclusive (X)') blocked_lock_request,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID blocked_sql_id,
obj.owner blocked_owner,
obj.object_name blocked_object_name,
obj.OBJECT_TYPE blocked_OBJECT_TYPE,
case
when b_s.ROW_WAIT_OBJ# <> -1 then
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
else
'-1'
end blocked_rowid, --被阻塞数据的rowid
decode(obj.object_type,
'TABLE',
'select * from ' || obj.owner || '.' || obj.object_name ||
' where rowid=''' ||
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#) || '''',
NULL) blocked_data_querysql

from gv$lock a,
gv$lock b,
gv$session a_s,
gv$session b_s,
dba_objects obj
where a.id1 = b.id1
and a.id2 = b.id2
and a.BLOCK > 0 --阻塞了其他人
and b.request > 0
and ((a.INST_ID = b.INST_ID and a.sid <> b.sid) or
(a.INST_ID <> b.INST_ID))
and a.sid = a_s.sid
and a.INST_ID = a_s.INST_ID
and b.sid = b_s.sid
and b.INST_ID = b_s.INST_ID
and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
order by a.inst_id,a.sid