RAC環境下的阻塞不同于單實例情形，因為我們需要考慮到位于不同實例的session。也就是說之前查詢的v$session，v$lock相應的應變化為全局范圍來查找。本文提供了2個查詢腳本，并給出實例演示那些session為阻塞者，哪些為被阻塞者。有關阻塞的概念以及單實例環境下的阻塞請參考：Oracle 阻塞(blocking blocked)

1、演示環境

scott@DEVDB> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production--在scott session中發布SQL語句，并未提交scott@DEVDB> begin 2 update emp set sal=sal+100 where empno=7788; 3 update dept set dname='DBA' where deptno=10; 4 end; 5 /PL/SQL procedure successfully completed.--在leshami session中更新emp對象leshami@DEVDB> update scott.emp set sal=sal-200 where empno=7788;--在usr1 session中更新emp對象usr1@DEVDB> update scott.dept set dname='DEV' where deptno=10;

2、尋找阻塞

scott@DEVDB> @block_session_racUSER_STATUS SID_SERIAL CONN_INSTANCE SID PROGRAMOSUSER MACHINELOCK_TYPE LOCK_MODE CTIME OBJECT_NAME--------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- -------------------------Blocking -> '20,1545' devdb120 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive666 DEPTBlocking -> '20,1545' devdb120 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive666 EMPWaiting'49,1007' devdb149 sqlplus@Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None618 EMPWaiting'933,11691' devdb2933 sqlplus@Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None558 DEPT--通過上述腳本我們可以看到session '20,1545' 鎖住了對象DEPT以及EMP，而此時session '49,1007'與'933,11691'處于等待狀態。--下面是另外的一種方式來獲取阻塞的情形scott@DEVDB> @block_session_rac2BLOCKING_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 :

3、演示中用到的腳本

[oracle@Linux-01 ~]$ more block_session_rac.sql set linesize 180col user_status format a15col sid_serial format a15col program format a30 wrappedcol machine format a15 wrappedcol osuser format a15 wrappedcol conn_instance format a15col 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_nameFROM gv$instanceWHERE 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 lJOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)JOIN gv$locked_object oON (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, TYPEFROM gv$lockWHERE request > 0)ORDER BY id1, id2, ctime DESC;[oracle@Linux-01 ~]$ more block_session_rac2.sql SELECT DISTINCTs1.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.sidAND s2.sid = l2.sidAND s1.inst_id = l1.inst_idAND s2.inst_id = l2.inst_idAND l1.block > 0AND l2.request > 0AND l1.id1 = l2.id1AND l1.id2 = l2.id2;