ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 管理LOCK的SQL

管理LOCK的SQL

原创 Linux操作系统 作者:xhailiang 时间:2008-02-17 19:15:20 0 删除 编辑

收集的和整合几个经常用到管理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#;


確定被BLOCKING的SESSION 等了多久:
SELECT LAST_CALL_ET  FROM V$SESSION WHERE SID=''

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-172025/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 笔记
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    425195