oracle 查看表空间

woxiangbo

浏览: 447682 次
性别:
来自: 上海

最近访客更多访客>>

junJZ_2008

zkm0309

walker_v5

czwlucky

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

column "USED_RATE(%)" for 9999.99;
col "FREE_SPACE(M)" for 999999999.99;
col "USED_SPACE(M)" for 999999999.99;

SELECT TABLESPACE_NAME, "SUM_SPACE(M)", SUM_BLOCKS, "USED_SPACE(M)", "USED_RATE(%)", "FREE_SPACE(M)"
  FROM (SELECT nvl(TABLESPACE_NAME, 'SUM(TBS)')TABLESPACE_NAME, SUM(SUM_SPACE) "SUM_SPACE(M)", SUM(SUM_BLOCKS) SUM_BLOCKS,
                SUM(USED_SPACE) "USED_SPACE(M)",
                round(SUM(USED_RATE) / COUNT(0) * 100, 2) "USED_RATE(%)",
                SUM(FREE_SPACE) "FREE_SPACE(M)", SUM(rn)
           FROM (SELECT TABLESPACE_NAME, SUM_SPACE, SUM_BLOCKS, USED_SPACE, "USED_RATE", nvl(FREE_SPACE,0) FREE_SPACE, ROWNUM rn
                    FROM ((SELECT D.TABLESPACE_NAME, SPACE SUM_SPACE, BLOCKS SUM_BLOCKS,
                                   SPACE - NVL(FREE_SPACE, 0) USED_SPACE, (1 - NVL(FREE_SPACE, 0) / SPACE) "USED_RATE",
                                   FREE_SPACE FREE_SPACE
                              FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS
                                       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 --if have tempfile 
                            SELECT D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)",
                                   NVL(USED_SPACE, 0) / SPACE  "USED_RATE(%)",
                                   NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
                              FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS
                                       FROM DBA_TEMP_FILES
                                      GROUP BY TABLESPACE_NAME) D,
                                   (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
                                            ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
                                       FROM V$TEMP_SPACE_HEADER
                                      GROUP BY TABLESPACE_NAME) F
                             WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)) ORDER BY "USED_RATE" DESC))
          GROUP BY ROLLUP(TABLESPACE_NAME)
          ORDER BY SUM(rn) ASC);

分享到：