Home > SQL Scripts und Tools > RAC Blocking Sessions

RAC Blocking Sessions

Oracle Database 11g

Ermitteln der blockierenden Sessions in einem RAC

col locking_instance for a16
col waiting_instance for a16

SELECT
    ih.instance_name || ' - ' ||  lh.sid        locking_instance
  , iw.instance_name || ' - ' ||  lw.sid        waiting_instance
  , DECODE (   lh.type
             , 'CF', 'Control File'
             , 'DX', 'Distrted Transaction'
             , 'FS', 'File Set'
             , 'IR', 'Instance Recovery'
             , 'IS', 'Instance State'
             , 'IV', 'Libcache Invalidation'
             , 'LS', 'LogStartORswitch'
             , 'MR', 'Media Recovery'
             , 'RT', 'Redo Thread'
             , 'RW', 'Row Wait'
             , 'SQ', 'Sequence #'
             , 'ST', 'Diskspace Transaction'
             , 'TE', 'Extend Table'
             , 'TT', 'Temp Table'
             , 'TX', 'Transaction'
             , 'TM', 'Dml'
             , 'UL', 'PLSQL User_lock'
             , 'UN', 'User Name'
             , 'Nothing-'
           )                                    waiter_lock_type
  , DECODE (   lw.request
             , 0, 'None'
             , 1, 'NoLock'
             , 2, 'Row-Share'
             , 3, 'Row-Exclusive'
             , 4, 'Share-Table'
             , 5, 'Share-Row-Exclusive'
             , 6, 'Exclusive'
             , 'Nothing-'
           )                                    waiter_mode_req
FROM
    gv$lock     lw
  , gv$lock     lh
  , gv$instance iw
  , gv$instance ih
WHERE
   iw.inst_id = lw.inst_id
  AND ih.inst_id = lh.inst_id
  AND lh.id1     = lw.id1
  AND lh.id2     = lw.id2
  AND lh.request = 0
  AND lw.lmode   = 0
  AND (lh.id1, lh.id2) IN ( SELECT id1,id2
                            FROM   gv$lock
                            WHERE  request = 0
                            INTERSECT
                            SELECT id1,id2
                            FROM   gv$lock
                            WHERE  lmode = 0
                          )
ORDER BY
    lh.sid
/