RAC環境下的阻塞不同于單實例情形，因為我們需要考慮到位于不同實例的session。也就是說之前查詢的v$session，v$lock相應的應變化為全局范圍來查找。本文提供了2個查詢腳本，并給出實例演示那些session為阻塞者，哪些為被阻塞者。有關阻塞的概念以及單實例環境下的阻塞請參考：Oracle 阻塞(blocking blocked)
scott@DEVDB> @block_session_rac
USER_STATUS SID_SERIAL CONN_INSTANCE SID PROGRAM OSUSER MACHINE LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME
--------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------
Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 DEPT
Blocking -> '20,1545' devdb1 20 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 666 EMP
Waiting '49,1007' devdb1 49 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMP
Waiting '933,11691' devdb2 933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT
--通過上述腳本我們可以看到session '20,1545' 鎖住了對象DEPT以及EMP，而此時session '49,1007'與'933,11691'處于等待狀態。
--下面是另外的一種方式來獲取阻塞的情形
scott@DEVDB> @block_session_rac2
BLOCKING_STATUS
----------------------------------------------------------------------------------------------------------------------------
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING USR1@Linux-02 ( INST=2 SID=933 Serial#=11691 )
SCOTT@Linux-01 ( INST=1 SID=20 Serail#=1545 ) IS BLOCKING LESHAMI@Linux-01 ( INST=1 SID=49 Serial#=1007 )
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
[oracle@Linux-01 ~]$ more block_session_rac.sql
set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
(SELECT instance_name
FROM gv$instance
WHERE inst_id = l.inst_id)
conn_instance,
s.sid,
s.program,
s.osuser,
s.machine,
DECODE (l.TYPE,
'RT', 'Redo Log Buffer',
'TD', 'Dictionary',
'TM', 'DML',
'TS', 'Temp Segments',
'TX', 'Transaction',
'UL', 'User',
'RW', 'Row Wait',
l.TYPE)
lock_type--,id1
--,id2
,
DECODE (l.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
LTRIM (TO_CHAR (lmode, '990')))
lock_mode,
ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
,
object_name
FROM gv$lock l
JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
JOIN gv$locked_object o
ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
JOIN dba_objects d ON (d.object_id = o.object_id)
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0)
ORDER BY id1, id2, ctime DESC;
[oracle@Linux-01 ~]$ more block_session_rac2.sql
SELECT DISTINCT
s1.username
|| '@'
|| s1.machine
|| ' ( INST='
|| s1.inst_id
|| ' SID='
|| s1.sid
|| ' Serail#='
|| s1.serial#
|| ' ) IS BLOCKING '
|| s2.username
|| '@'
|| s2.machine
|| ' ( INST='
|| s2.inst_id
|| ' SID='
|| s2.sid
|| ' Serial#='
|| s2.serial#
|| ' ) '
AS blocking_status
FROM gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND s1.inst_id = l1.inst_id
AND s2.inst_id = l2.inst_id
AND l1.block > 0
AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
