开源中文网

您的位置: 首页 > 数据库应用 > Oracle > 正文

数据库监控用到的sql

来源:  作者:

-1.查询被X锁的记录信息
SELECT S.SID, O.OBJECT_NAME, F.NAME FILE_NAME, S.ROW_WAIT_BLOCK# LOCKED_BLOCK, S.ROW_WAIT_ROW# LOCKED_ROW
  FROM V$SESSION S, USER_OBJECTS O, V$DATAFILE F
WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID
   AND S.ROW_WAIT_FILE# = F.FILE#
   AND S.SID IN (SELECT DISTINCT SID FROM V$LOCK WHERE REQUEST = 6)
--2.查询某用户每个表占用的空间大小(owner需要根据实际情况录入,注意大写)
SELECT OWNER 用户,
       SEGMENT_NAME 段名,
       PARTITION_NAME 分区名,
       SEGMENT_TYPE 段类型,
       TABLESPACE_NAME 表空间,
       BYTES 大小b,
       BYTES / 1024 / 1024 大小m
  FROM DBA_SEGMENTS
WHERE OWNER = 'TEST';
--3.表空间占用率的统计
SELECT TABLESPACE_NAME, --表空间名称
       TO_CHAR(ROUND(SUM_MAX / 1024 / 1024, 2)) || ' M' AS CAPA_MAX, --从扩充后角度看,最大大小
       TO_CHAR(ROUND((SUM_ALLOC - NVL(SUM_FREE, 0)) / 1024 / 1024, 2)) || ' M' AS CAPA_USED, --目前使用空间大小
       TO_CHAR(ROUND((SUM_MAX - SUM_ALLOC + NVL(SUM_FREE, 0)) / 1024 / 1024,
                     2)) || ' M' AS CAPA_FREE, --从扩充角度看,剩余空间大小
       TO_CHAR(ROUND(100 * (SUM_ALLOC - NVL(SUM_FREE, 0)) / SUM_MAX, 2)) || '%' AS CAPA_USED_P, --从扩充角度看,占用空间百分率
       TO_CHAR(ROUND(100 * (SUM_MAX - SUM_ALLOC + NVL(SUM_FREE, 0)) /
                     SUM_MAX,
                     2)) || '%' AS CAPA_PER_P, --从扩充角度看,剩余空间百分率
       TO_CHAR(ROUND(SUM_ALLOC / 1024 / 1024, 2)) || ' M' AS MAX, --目前分配空间大小
       TO_CHAR(ROUND((SUM_ALLOC - NVL(SUM_FREE, 0)) / 1024 / 1024, 2)) || ' M' AS USED, --目前使用空间大小
       TO_CHAR(ROUND(NVL(SUM_FREE, 0) / 1024 / 1024, 2)) || ' M' AS FREE, --目前剩余空间大小
       TO_CHAR(ROUND(100 * (SUM_ALLOC - NVL(SUM_FREE, 0)) / SUM_ALLOC, 2)) || '%' AS USED_P, --目前空间使用百分比
       TO_CHAR(ROUND(100 * NVL(SUM_FREE, 0) / SUM_ALLOC, 2)) || '%' AS PER_P --目前空间剩余百分比
  FROM (SELECT TABLESPACE_NAME,
               SUM(BYTES) AS SUM_ALLOC, --分配空间大小
               SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) AS SUM_MAX --扩展后大小
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME),
       (SELECT TABLESPACE_NAME AS FS_TS_NAME, SUM(BYTES) AS SUM_FREE --空间空间大小
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME)
WHERE TABLESPACE_NAME = FS_TS_NAME(+);

Tags:数据库 监控 用到
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明