Query to find tablespace usage above 80%

Please find the 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,
                 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;

Comments