RAC环境下的阻塞不同于单实例情形，因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本，并给出实例演示那些session为阻塞者，哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考：Oracle 阻塞(blocking blocked)

1、演示环境

[sql]view plaincopyprint?scott@DEVDB>select*fromv$versionwhererownum<2;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.3.0-64bitProduction--在scottsession中发布SQL语句，并未提交scott@DEVDB>begin2updateempsetsal=sal+100whereempno=7788;3updatedeptsetdname='DBA'wheredeptno=10;4end;5/PL/SQLproceduresuccessfullycompleted.--在leshamisession中更新emp对象leshami@DEVDB>updatescott.empsetsal=sal-200whereempno=7788;--在usr1session中更新emp对象usr1@DEVDB>updatescott.deptsetdname='DEV'wheredeptno=10;

2、寻找阻塞

[sql]view plaincopyprint?scott@DEVDB>@block_session_racUSER_STATUSSID_SERIALCONN_INSTANCESIDPROGRAMOSUSERMACHINELOCK_TYPELOCK_MODECTIMEOBJECT_NAME-------------------------------------------------------------------------------------------------------------------------------------------------------------------Blocking->'20,1545'devdb120sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionExclusive666DEPTBlocking->'20,1545'devdb120sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionExclusive666EMPWaiting'49,1007'devdb149sqlplus@Linux-01(TNSV1-V3)oracleLinux-01TransactionNone618EMPWaiting'933,11691'devdb2933sqlplus@Linux-02(TNSV1-V3)oracleLinux-02TransactionNone558DEPT--通过上述脚本我们可以看到session'20,1545'锁住了对象DEPT以及EMP，而此时session'49,1007'与'933,11691'处于等待状态。--下面是另外的一种方式来获取阻塞的情形scott@DEVDB>@block_session_rac2BLOCKING_STATUS----------------------------------------------------------------------------------------------------------------------------SCOTT@Linux-01(INST=1SID=20Serail#=1545)ISBLOCKINGUSR1@Linux-02(INST=2SID=933Serial#=11691)SCOTT@Linux-01(INST=1SID=20Serail#=1545)ISBLOCKINGLESHAMI@Linux-01(INST=1SID=49Serial#=1007)--Author:Leshami--Blog:http://blog.csdn.net/leshami

3、演示中用到的脚本

[sql]view plaincopyprint?[oracle@Linux-01~]$moreblock_session_rac.sqlsetlinesize180coluser_statusformata15colsid_serialformata15colprogramformata30wrappedcolmachineformata15wrappedcolosuserformata15wrappedcolconn_instanceformata15colobject_nameformata25wrappedSELECTDECODE(l.block,0,'Waiting','Blocking->')user_status,CHR(39)||s.sid||','||s.serial#||CHR(39)sid_serial,(SELECTinstance_nameFROMgv$instanceWHEREinst_id=l.inst_id)conn_instance,s.sid,s.program,s.osuser,s.machine,DECODE(l.TYPE,'RT','RedoLogBuffer','TD','Dictionary','TM','DML','TS','TempSegments','TX','Transaction','UL','User','RW','RowWait',l.TYPE)lock_type--,id1--,id2,DECODE(l.lmode,0,'None',1,'Null',2,'RowShare',3,'RowExcl.',4,'Share',5,'S/RowExcl.',6,'Exclusive',LTRIM(TO_CHAR(lmode,'990')))lock_mode,ctime--,DECODE(l.BLOCK,0,'NotBlocking',1,'Blocking',2,'Global')lock_status,object_nameFROMgv$locklJOINgv$sessionsON(l.inst_id=s.inst_idANDl.sid=s.sid)JOINgv$locked_objectoON(o.inst_id=s.inst_idANDs.sid=o.session_id)JOINdba_objectsdON(d.object_id=o.object_id)WHERE(l.id1,l.id2,l.TYPE)IN(SELECTid1,id2,TYPEFROMgv$lockWHERErequest>0)ORDERBYid1,id2,ctimeDESC;[oracle@Linux-01~]$moreblock_session_rac2.sqlSELECTDISTINCTs1.username||'@'||s1.machine||'(INST='||s1.inst_id||'SID='||s1.sid||'Serail#='||s1.serial#||')ISBLOCKING'||s2.username||'@'||s2.machine||'(INST='||s2.inst_id||'SID='||s2.sid||'Serial#='||s2.serial#||')'ASblocking_statusFROMgv$lockl1,gv$sessions1,gv$lockl2,gv$sessions2WHEREs1.sid=l1.sidANDs2.sid=l2.sidANDs1.inst_id=l1.inst_idANDs2.inst_id=l2.inst_idANDl1.block>0ANDl2.request>0ANDl1.id1=l2.id1ANDl1.id2=l2.id2;