RAC 环境下查询阻塞情况，并生成kill session 语句，在一个节点即可杀掉所有blocker session

-- RAC 环境下的阻塞查杀

select 'alter system kill session ' || chr(39) || sh.sid || ',' ||

sh.SERIAL# || ',@' || sh.INST_ID || chr(39) || ';' as "kill blocker on command line",

sw.SQL_ID blockd_sql_id,

lw.CTIME wait_second,

lh.sid||','||sh.SERIAL#||'@'||lh.INST_ID ||' is blocking '||lw.SID||','||sw.serial#||'@'||lw.INST_ID,

qw.SQL_TEXT blocked_sql,

qw.SQL_FULLTEXT blocked_sql_full_text

from gv$lock lh, gv$lock lw, gv$session sw, gv$session sh, gv$sql qw

where lh.id1 = lw.id1

and lh.id2 = lw.id2

and lw.type in ('TM', 'TX')

and lh.kaddr <> lw.KADDR

and lh.TYPE = lw.TYPE

and lw.LMODE = 0

and lw.INST_ID = sw.INST_ID

and lw.sid = sw.SID

and lh.INST_ID = sh.INST_ID

and lh.SID = sh.SID

and sw.SQL_ID = qw.SQL_ID

and lw.CTIME > 2;

主要通过gv$ 视图生成全局的锁信息，kill 语句 通过@实例名 杀别的实例上的会话。但是gv$ 视图关联速度略慢。