Query to find tablespace usage above 80%

set line 200
SELECT d.tablespace_name “TS Name”, d.num_files “Num Files”,
d.asize “Size MB”, NVL (f.freebytes, 0) “Free MB”,
(d.BYTES – NVL (f.freebytes, 0)) “Used MB”,
((d.BYTES – NVL (f.freebytes, 0)) * 100) / d.asize “Percent Used”
FROM (SELECT tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes
FROM dba_free_space
GROUP BY tablespace_name) f,
(SELECT tablespace_name, COUNT (DISTINCT (file_id)) num_files,
sum(greatest(maxbytes,bytes))/1024/1024 asize,
/* Below logic does not calculate size properly if maxbytes is less than bytes
SUM (DECODE (maxbytes, 0, BYTES, maxbytes))
/ 1024
/ 1024 asize,
*/
SUM (BYTES) / 1024 / 1024 BYTES
FROM dba_data_files
GROUP BY tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name not like ‘%UNDO%’
AND ROUND (((d.BYTES – NVL (f.freebytes, 0)) * 100) / d.asize) > 80;