oracle 数据库 查看表空间剩余 计算自动扩展

oracle 查看表空间剩余 计算自动扩展

auth wangjing
data 2018-08-08

说明：

此视图计算了自动扩展的数据文件的最大字节数。

视图语句

CREATE OR REPLACE VIEW SYNC.VU_TABLESPACE_WANGJING AS
SELECT A.TABLESPACE_NAME                                                           "TABLESPACENAME",
       TO_CHAR(DECODE(MAX_, 0, TOTAL, MAX_)                        ,'fm999990.00') "MAXSIZE",
       TO_CHAR(TOTAL - FREE                                        ,'fm999990.00') "USED",
       TO_CHAR(DECODE(MAX_, 0, TOTAL, MAX_) - (TOTAL - FREE)       ,'fm999990.00') "AVAIL",
       TO_CHAR(100 * (TOTAL - FREE) / DECODE(MAX_, 0, TOTAL, MAX_) ,'fm999990.00') "USE%"
  FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)   / 1024 / 1024 / 1024, 2) FREE  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)   / 1024 / 1024 / 1024, 2) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B,
       (SELECT TABLESPACE_NAME, SUM(MAX1) MAX_ FROM (SELECT TABLESPACE_NAME, ROUND((DECODE(AUTOEXTENSIBLE, 'YES', SUM(MAXBYTES), 0) +
        DECODE(AUTOEXTENSIBLE, 'NO', SUM(BYTES), 0)) / 1024 / 1024 / 1024, 2) MAX1 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE) GROUP BY TABLESPACE_NAME) C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
 ORDER BY 100 * (TOTAL - FREE) / DECODE(MAX_, 0, TOTAL, MAX_) DESC;
comment on table  SYNC.VU_TABLESPACE_WANGJING is '表空间统计视图 by WangJing';
comment on column SYNC.VU_TABLESPACE_WANGJING.TABLESPACENAME is '表空间名称';
comment on column SYNC.VU_TABLESPACE_WANGJING.MAXSIZE is '最大扩展容量(G)';
comment on column SYNC.VU_TABLESPACE_WANGJING.USED is '已使用大小(G)';
comment on column SYNC.VU_TABLESPACE_WANGJING.AVAIL is '可用空间大小(G)';
comment on column SYNC.VU_TABLESPACE_WANGJING."USE%" is '已使用百分比（默认排序）';

如无法创建此视图 请赋予以下权限

GRANT SELECT ANY DICTIONARY TO SYNC;