SELECT sumfiles.dfts "Tablespace",
round(sumfiles.fullsiz,2) "Allocated MB",
round(sumfiles.maxsiz,2) "Max Bytes MB",
round(sumfiles.fullsiz-nvl(sumfree.free,0),2) "Used MB",
round(nvl(sumfree.free,0),2) "Free MB",
round(((sumfiles.fullsiz - nvl(sumfree.free,0))/sumfiles.fullsiz)*100,2) "% Used",
round(((sumfiles.fullsiz - nvl(sumfree.free,0))/sumfiles.maxsiz)*100,2) "% MAX Used"
FROM
(SELECT
tablespace_name ts,
sum(bytes)/1024/1024 free
FROM dba_free_space
WHERE tablespace_name not in ('TEMP')
GROUP BY tablespace_name) sumfree,
(SELECT
tablespace_name dfts,
sum(bytes)/1024/1024 fullsiz,
sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 maxsiz
FROM dba_data_files
WHERE tablespace_name not in ('TEMP')
GROUP BY tablespace_name) sumfiles
WHERE sumfiles.dfts = sumfree.ts (+)
ORDER BY 1
/
