ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 检查锁的 scripts

检查锁的 scripts

原创 Linux操作系统 作者:xhailiang 时间:2006-11-09 00:00:00 0 删除 编辑
Here is a script that could help a DBA to see which session is blocking another and on which row exactly. Script to list all blocking lock:
SET serverout on size 1000000
SET lines 132

DECLARE
   CURSOR cur_lock
   IS
      SELECT SID, id1, id2, inst_id, ctime
        FROM gv$lock
       WHERE BLOCK = 1;

   vid1       NUMBER;
   vid2       NUMBER;

   CURSOR cur_locked
   IS
      SELECT SID, inst_id, ctime
        FROM gv$lock
       WHERE id1 = vid1
         AND id2 = vid2
         AND BLOCK <> 1;

   vlocks     VARCHAR2 (30);
   vsid1      NUMBER;
   vobj1      NUMBER;
   vfil1      NUMBER;
   vblo1      NUMBER;
   vrow1      NUMBER;
   vrowid1    VARCHAR2 (20);
   vcli1      VARCHAR2 (64);
   vobj2      NUMBER;
   vfil2      NUMBER;
   vblo2      NUMBER;
   vrow2      NUMBER;
   vrowid2    VARCHAR2 (20);
   vcli2      VARCHAR2 (64);
   vobjname   VARCHAR2 (30);
   vlocked    VARCHAR2 (30);
   ctim1      NUMBER;
   ctim2      NUMBER;
BEGIN
   DBMS_OUTPUT.put_line
      ('====================================================='
      );
   DBMS_OUTPUT.put_line ('Blocking lock list.');
   DBMS_OUTPUT.put_line
      ('====================================================='
      );
   DBMS_OUTPUT.put_line
      ('Block / Is blocked
               SID
               INST_ID OBJECT
                                TIME(secs) ROWID
                     CLIENT_IDENTIFIER'
      );
   DBMS_OUTPUT.put_line
      ('-------------------------
        ---------
          -------
           ------------------------------
            ----------
             ------------------ -----------------'
      );

   FOR c1 IN cur_lock
   LOOP
      vid1 := c1.id1;
      vid2 := c1.id2;

      SELECT username, SID, row_wait_obj#,
             row_wait_file#, row_wait_block#,
             row_wait_row#, client_identifier
        INTO vlocks, vsid1, vobj1,
             vfil1, vblo1,
             vrow1, vcli1
        FROM gv$session
       WHERE SID = c1.SID AND inst_id = c1.inst_id;

      IF vobj1 = -1
      THEN
         vobjname := 'UNKNOWN';
      ELSE
         SELECT NAME
           INTO vobjname
           FROM SYS.obj$
          WHERE obj# = vobj1;

         SELECT DECODE
                   (vrow1,
                    0, 'MANY ROWS',
                    DBMS_ROWID.rowid_create
                                           (1,
                                            vobj1,
                                            vfil1,
                                            vblo1,
                                            vrow1
                                           )
                   )
           INTO vrowid1
           FROM DUAL;
      END IF;

      DBMS_OUTPUT.put_line (   RPAD (vlocks, 25)
                            || ' '
                            || TO_CHAR
                                      (vsid1,
                                       '999999999'
                                      )
                            || ' '
                            || TO_CHAR
                                      (c1.inst_id,
                                       '9999999'
                                      )
                            || ' '
                            || RPAD (vobjname, 30)
                            || ' '
                            || TO_CHAR
                                      (c1.ctime,
                                       '999999999'
                                      )
                            || ' '
                            || RPAD (vrowid1, 18)
                            || ' '
                            || vcli1
                           );

      FOR c2 IN cur_locked
      LOOP
         SELECT username, row_wait_obj#,
                row_wait_file#, row_wait_block#,
                row_wait_row#
           INTO vlocked, vobj2,
                vfil2, vblo2,
                vrow2
           FROM gv$session
          WHERE SID = c2.SID
            AND inst_id = c2.inst_id;

         IF vobj2 = -1
         THEN
            vobjname := 'UNKNOWN';
         ELSE
            SELECT NAME
              INTO vobjname
              FROM SYS.obj$
             WHERE obj# = vobj2;

            SELECT DECODE
                      (vrow2,
                       0, 'MANY ROWS',
                       DBMS_ROWID.rowid_create
                                           (1,
                                            vobj2,
                                            vfil2,
                                            vblo2,
                                            vrow2
                                           )
                      )
              INTO vrowid2
              FROM DUAL;
         END IF;

         DBMS_OUTPUT.put_line
                           (   CHR (9)
                            || '--> '
                            || RPAD (vlocked, 12)
                            || ' '
                            || TO_CHAR
                                      (c2.SID,
                                       '999999999'
                                      )
                            || ' '
                            || TO_CHAR
                                      (c2.inst_id,
                                       '9999999'
                                      )
                            || ' '
                            || RPAD (vobjname, 30)
                            || ' '
                            || TO_CHAR
                                      (c2.ctime,
                                       '999999999'
                                      )
                            || ' '
                            || RPAD (vrowid2, 18)
                            || ' '
                            || vcli2
                           );
      END LOOP;
   END LOOP;

   COMMIT;
END;
/

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

上一篇: RMAN备份恢复测试
请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    414446