ITPub博客

首页 > 数据库 > Oracle > oracle万能表空间使用率查询SQL

oracle万能表空间使用率查询SQL

原创 Oracle 作者:libingquan008 时间:2015-09-03 17:37:41 0 删除 编辑

     在有些情况下表空间的数据文件既有自动扩展的也有非自动扩展的,用普通的表空间容量查询语句得到的结果一般不准确,为了解决这个问题我进行了区分统计,首先得到TOTAL_MB这个列,TOTAL_MB的值来自于数据文件的BYTES(非自动扩展)与数据文件最大值32G(自动扩展)的和;然后再用dba_segments取到每一个表空间下含有所有段的大小总和也就是USED_MB. 这连个值都取到以后,表空间的剩余量和使用率就自然得到了:参考下面

点击(此处)折叠或打开

  1. select F0.TBNAME,
  2.        F0.TOTAL_MB,
  3.        NVL(F1.USED_MB,0) TOTAL_USED_MB,
  4.        (F0.TOTAL_MB - NVL(F1.USED_MB,0)) TOTAL_FREE_MB,
  5.        round((NVL(F1.USED_MB,0) / F0.TOTAL_MB) * 100, 2) || '%' "%USED"
  6.   from
  7.   (select t1.tbname TBNAME, sum(t1.MBYTES) TOTAL_MB
  8.           from (select tablespace_name tbname,
  9.                        (case autoextensible
  10.                          when 'YES' then
  11.                           32768
  12.                          when 'NO' then
  13.                           bytes / 1024 / 1024
  14.                        end) MBYTES
  15.                   from dba_data_files
  16.                  group by tablespace_name, autoextensible, bytes) t1
  17.          group by t1.tbname) F0,
  18.        (select tablespace_name TBNAME,
  19.                round(NVL(sum(bytes /1024/ 1024),0), 2) USED_MB
  20.           from dba_segments
  21.          group by tablespace_name) F1
  22.  where F0.TBNAME = F1.TBNAME(+);


下面这条统计表空间使用率的SQL也很强大,加上了对临时表空间查询,但没有做到自动区分数据文件是自动扩展还是非自动扩展的。不妨参考下:

点击(此处)折叠或打开

  1. SELECT D.TABLESPACE_NAME,
  2.        SPACE || 'M' "SUM_SPACE(M)",
  3.        BLOCKS "SUM_BLOCKS",
  4.        SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
  5.        ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
  6.        FREE_SPACE || 'M' "FREE_SPACE(M)"
  7.   FROM (SELECT TABLESPACE_NAME,
  8.                ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
  9.                SUM(BLOCKS) BLOCKS
  10.           FROM DBA_DATA_FILES
  11.          GROUP BY TABLESPACE_NAME) D,
  12.        (SELECT TABLESPACE_NAME,
  13.                ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
  14.           FROM DBA_FREE_SPACE
  15.          GROUP BY TABLESPACE_NAME) F
  16.  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
  17. UNION ALL --如果有临时表空间
  18. SELECT D.TABLESPACE_NAME,
  19.        SPACE || 'M' "SUM_SPACE(M)",
  20.        BLOCKS SUM_BLOCKS,
  21.        USED_SPACE || 'M' "USED_SPACE(M)",
  22.        ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
  23.        NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
  24.   FROM (SELECT TABLESPACE_NAME,
  25.                ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
  26.                SUM(BLOCKS) BLOCKS
  27.           FROM DBA_TEMP_FILES
  28.          GROUP BY TABLESPACE_NAME) D,
  29.        (SELECT TABLESPACE_NAME,
  30.                ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
  31.                ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
  32.           FROM V$TEMP_SPACE_HEADER
  33.          GROUP BY TABLESPACE_NAME) F
  34.  WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
  35.  ORDER BY 1;






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

请登录后发表评论 登录
全部评论

注册时间:2011-02-10

  • 博文量
    46
  • 访问量
    119538