oracle脚本整理4

脚本来监控数据库中锁的状况
3.2.1  showlock.sql

第一个脚本showlock.sql，该脚本通过连接v$locked_object与all_objects两视图，显示哪些对象被哪些会话锁住：

/* showlock.sql */

column o_name format a10

column lock_type format a20

column object_name format a15

select rpad(oracle_username,10) o_name,session_id sid,

       decode(locked_mode,0,'None',1,'Null',2,'Row share',

       3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,

       object_name ,xidusn,xidslot,xidsqn

from v$locked_object,all_objects

where v$locked_object.object_id=all_objects.object_id;

3.2.2  showalllock.sql

第二个脚本showalllock.sql，该脚本主要显示当前所有TM、TX锁的信息；

/* showalllock.sql */

select sid,type,id1,id2,

        decode(lmode,0,'None',1,'Null',2,'Row share',

        3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')

        lock_type,request,ctime,block

from v$lock

where TYPE IN('TX','TM');

3.2.2 showlockorder.sql
改进上一个脚本，将type,id1,id2合并，并安该字段和ctime时间排序
/* showlockorder.sql */
break on resource
column sid format 9999
column resource format a15
column request format a15

SELECT    TYPE || '-' || id1 || '-' || id2 "resource", SID,
          DECODE (lmode,
                  0, 'None',
                  1, 'Null',
                  2, 'Row share',
                  3, 'Row Exclusive',
                  4, 'Share',
                  5, 'Share Row Exclusive',
                  6, 'Exclusive'
                 ) lock_type,
          DECODE (request,
                  0, 'None',
                  1, 'Null',
                  2, 'Row share',
                  3, 'Row Exclusive',
                  4, 'Share',
                  5, 'Share Row Exclusive',
                  6, 'Exclusive'
                 ) request,
          ctime, BLOCK
     FROM v$lock
    WHERE TYPE IN ('TX', 'TM')
ORDER BY "resource", ctime DESC
/
clear breaks