记不住，就写下呗！

管理LOCK的SQL

上一篇 / 下一篇 2008-02-17 19:15:20 / 精华(3) / 置顶(3) / 不允许评论 / 个人分类：OraTips

收集的和整合几个经常用到管理LOCK的SQL.

定位誰鎖住誰 :

select /*+ ordered*/
           (select username from v$session where sid=a.sid) blocker,
      a.sid,
      ' is blocking ',
       (select username from v$session where sid=b.sid) blockee,
          b.sid
    from v$lock a, v$lock b
   where a.block = 1
     and b.request > 0
     and a.id1 = b.id1
     and a.id2 = b.id2

select /*+ ordered */ w1.sid waiting_session,
    h1.sid holding_session,
    w.kgllktype lock_or_pin,
        w.kgllkhdl address,
    decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
       'Unknown') mode_held,
    decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
       'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
   and
     (((w.kgllkmod = 0) or (w.kgllkmod= 1))
     and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype     = h.kgllktype
and w.kgllkhdl     = h.kgllkhdl
and w.kgllkuse     =   w1.saddr
and h.kgllkuse     =   h1.saddr
/

確定一個transaction 鎖定了幾行：
SELECT * FROM V$TRANSACTION
WHERE (XIDUSN,XIDSLOT,XIDSQN) IN (SELECT XIDUSN,XIDSLOT,XIDSQN FROM v$locked_object )

找出被BLOCK住的SESSION 在等哪一行的鎖：
SELECT a.session_id,c.schemaname||'.'||b.object_name object_name,
       DBMS_ROWID.rowid_create (1,
                                b.data_object_id,
                                c.row_wait_file#,
                                c.row_wait_block#,
                                c.row_wait_row#
                               ) object_rowid, c.LAST_CALL_ET ,
       c.row_wait_obj#,
       c.row_wait_file#,
       c.row_wait_block#,
       c.row_wait_row#
FROM v$locked_object a, dba_objects b, v$session c
where a.session_id = c.sid
   and a.object_id = b.OBJECT_ID
   and a.object_id = c.row_wait_obj#
   and c.row_wait_obj# <> -1

SELECT /*+ no_merge(lo) */
       DO.object_name, lo.SESSION_ID, lo.oracle_username, lo.OS_USER_NAME,
       DECODE(locked_mode,
              1, 'SELECT',
              2, 'SELECT FOR UPDATE / LOCK ROW SHARE',
              3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE',
              4, 'CREATE INDEX/LOCK SHARE',
              5, 'LOCK SHARE ROW EXCLUSIVE',
              6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK EXCLUSIVE') sql_actions,
       DECODE(locked_mode, 1, 'NULL', 2, 'SS - SUB SHARE', 3, 'SX - SUB EXCLUSIVE',
              4, 'S - SHARE', 5, 'SSX - SHARE/SUB EXCLUSIVE', 6, 'X - EXCLUSIVE') Lock_mode
FROM sys.V_$LOCKED_OBJECT lo, DBA_OBJECTS DO
WHERE DO.object_id = lo.object_id;

SELECT owner obj_owner,
       object_name obj_name,
       object_type obj_type,
       dbms_rowid.rowid_create(1, data_object_id, ROW_WAIT_FILE#,
                               ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) obj_rowid,
       a.username db_user, a.SID SID, a.TYPE lock_type,
       a.row_wait_file#, a.row_wait_block#, a.row_wait_row#
FROM DBA_OBJECTS,
       (SELECT /*+ no_merge(a) no_merge(b) */
               a.username, a.SID, a.row_wait_obj#, a.ROW_WAIT_FILE#,
               a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#, b.TYPE
          FROM sys.V_$SESSION a, sys.V_$LOCK b
         WHERE a.username IS NOT NULL
           AND a.row_wait_obj# <> -1
           AND a.SID = b.SID
           AND b.TYPE IN ('TX','TM')
           ) a
WHERE object_id = a.row_wait_obj#;