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

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;
案例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