SELECT E.TABLESPACE_NAME,
ROUND(SUM(E.TOTAL_SPACE), 2) "TOTAL_SPACE(G)",
ROUND(SUM(E.FREE_SPACE), 2) "FREE_SPACE(G)",
ROUND((1 - SUM(E.FREE_SPACE) / SUM(E.TOTAL_SPACE)) * 100, 2) "USED_RATE(%)"
FROM (SELECT A.FILE_ID,
A.TABLESPACE_NAME,
DECODE(A.AUTOEXTENSIBLE, 'YES', A.MAXBYTES, A.BYTES) /
(1024 * 1024 * 1024) TOTAL_SPACE,
DECODE(A.AUTOEXTENSIBLE,
'YES',
A.MAXBYTES - A.BYTES + D.F_SPACE,
D.F_SPACE) / (1024 * 1024 * 1024) FREE_SPACE
FROM DBA_DATA_FILES A,
(SELECT B.FILE_ID, SUM(B.BYTES) F_SPACE
FROM DBA_FREE_SPACE B
GROUP BY B.FILE_ID) D
WHERE A.FILE_ID = D.FILE_ID(+)) E
GROUP BY E.TABLESPACE_NAME
UNION ALL
SELECT E.TABLESPACE_NAME,
ROUND(SUM(E.TOTAL_SPACE), 2) "TOTAL_SPACE(G)",
ROUND(SUM(E.FREE_SPACE), 2) "FREE_SPACE(G)",
ROUND((1 - SUM(E.FREE_SPACE) / SUM(E.TOTAL_SPACE)) * 100, 2) "USED_RATE(%)"
FROM (SELECT A.FILE_ID,
A.TABLESPACE_NAME,
ROUND(DECODE(A.AUTOEXTENSIBLE, 'YES', A.MAXBYTES, A.BYTES) /
(1024 * 1024 * 1024),
2) TOTAL_SPACE,
ROUND(DECODE(A.AUTOEXTENSIBLE,
'YES',
A.MAXBYTES - A.BYTES + D.F_SPACE,
D.F_SPACE) / (1024 * 1024 * 1024),
2) FREE_SPACE
FROM DBA_TEMP_FILES A,
(SELECT B.FILE_ID, SUM(B.BYTES_FREE) F_SPACE
FROM V$TEMP_SPACE_HEADER B
GROUP BY B.FILE_ID) D
WHERE A.FILE_ID = D.FILE_ID(+)) E
GROUP BY E.TABLESPACE_NAME
ORDER BY 4 DESC;