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);