ITPub博客

首页 > 数据库 > Oracle > 查询表空间使用情况

查询表空间使用情况

原创 Oracle 作者:aidway 时间:2014-04-11 10:57:54 0 删除 编辑

col "表空间" for a10
col "总量(M)" for 99999999
col "已用(M)" for 99999999
col "剩余(M)" for 99999999
col "使用率(%)"  for 99.99
set linesize 200
set pages 99

SELECT D.TABLESPACE_NAME "表空间",
       MAXBYTES "总量(M)",
       SPACE - NVL(FREE_SPACE, 0) "已用(M)",
       MAXBYTES - (SPACE - NVL(FREE_SPACE, 0)) "剩余(M)",
       ROUND((SPACE - NVL(FREE_SPACE, 0)) / MAXBYTES * 100, 2) "使用率(%)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) /
                     (1024 * 1024),
                     2) MAXBYTES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME "表空间",
       MAXBYTES "总量(M)",
       NVL(USED_SPACE, 0) "已用(M)",
       MAXBYTES - NVL(USED_SPACE, 0) "剩余(M)",
       ROUND(NVL(USED_SPACE, 0) / MAXBYTES * 100, 2) "使用率(%)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               ROUND(SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) /
                     (1024 * 1024),
                     2) MAXBYTES
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE,
               ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
          FROM V$TEMPSEG_USAGE
         GROUP BY TABLESPACE) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)
 ORDER BY "使用率(%)" DESC;

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

上一篇: 索引相关
下一篇: 归档相关
请登录后发表评论 登录
全部评论

注册时间:2013-12-04

  • 博文量
    28
  • 访问量
    89975