统计增加表空见两种方式的数据文件大小总值,也就是统计表空间总大小
WITH TABLESPACE_TOTAL AS
(
SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='YES' group by tablespace_name
UNION ALL
SELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='NO' group by tablespace_name
)
SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME;
表空间已使用的大小
select TABLESPACE_NAME,sum(bytes) from dba_segments
where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
GROUP BY TABLESPACE_NAME;