----自动扩展表空间的使用率
col tablespace_name for a25
col status for a10
SELECT D.TABLESPACE_NAME,G.STATUS,
MAX_SPACE,
SPACE "SUM_SPACE(M)",
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(MAX_SPACE) MAX_SPACE,
SUM(SPACE) SPACE,
SUM(BLOCKS) BLOCKS
FROM (SELECT TABLESPACE_NAME,
ROUND(decode(AUTOEXTENSIBLE,
'YES',
SUM(MAXBYTES) / (1024 * 1024),
SUM(BYTES) / (1024 * 1024)),
2) MAX_SPACE,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME, AUTOEXTENSIBLE)
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,
dba_tablespaces G
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = G.TABLESPACE_NAME
order by 6 desc;
