SELECT UPPER(F.TABLESPACE_NAME) tbsname,
D.TOT_GROOTTE_MB total,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES used,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') used_pct,
F.TOTAL_BYTES free
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) as TOTAL_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

select a.tablespace_name ,
trunc(total / 1024 / 1024/ 1024, 2) "SUM_SPACE/G",
trunc((total - nvl(free, 0)) / 1024 / 1024/ 1024, 2) "USED_SPACE/G",
trunc(nvl(free, 0) / 1024 / 1024/ 1024, 2) "FREE_SPACE/G",
trunc((total - nvl(free, 0)) * 100 / total, 2) "USED_RATE/%",
trunc(maxsize / 1024 / 1024/ 1024, 2) "MAX_SUM_SPACE/G",
trunc((total - nvl(free, 0)) * 100 / maxsize, 2) "MAX_SUM_SPACE/%",
file_count "FILE_COUNT"
from (select tablespace_name,
sum(bytes) total,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxsize,
count(file_name) file_count
from dba_data_files
group by tablespace_name) a
left join (select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) b
on a.tablespace_name = b.tablespace_name
order by 7 desc;