【Oracle脚本】检查Oracle数据库的DX锁

浏览数：50 / 时间：2015年06月12日

set linesize 300
set pagesize 200
column  blocker  format a10
column  blockee  format a10
column  blocker_sql format a30
column  blockee_sql format a30
column  blocker_machine format a30
column  blocker_program format a30
column  blocker_event format a30
column  blockee_machine format a30
column  blockee_program format a30 
column  blockee_event format a30
SELECT s1.username            "WAIT",
       s1.machine             "Machine",
       w.sid,
       s1.serial#,
       s1.SQL_ID,
       trim(P1.spid)                 "PID",
--       S1.INST_ID            "INSTANCE",
       S1.INST_ID             "IN",
       s2.username             "HOLD",
       s2.machine              "Machine",
       h.sid,
       s2.serial#,
       s2.sql_id,
       trim(p2.spid)                  "PID",
--       S2.INST_ID             "INSTANCE",
       S2.INST_ID              "IN",
       S2.PROCESS
FROM   gv$process P1,    gv$process P2,
       gv$session S1,    gv$session S2,
       gv$lock w,          gv$lock h
WHERE
  (((h.LMODE != 0) and (h.LMODE != 1)
  and ((h.REQUEST = 0) or (h.REQUEST = 1)))
  and  (((w.LMODE= 1) or (w.LMODE = 0))
  and ((w.REQUEST != 1) and (w.REQUEST != 0))))
  and  w.type =  h.type
  and  w.id1  =  h.id1
  and  w.id2  =  h.id2
  and  w.sid     !=  h.sid
  and  w.sid       = S1.sid
  and  h.sid       = S2.sid
  and  S1.EVENT  =‘enq: DX - contention‘
  AND    S1.paddr           = P1.addr
  AND    S2.paddr           = P2.addr
  order by "WAIT",h.CTIME;