Script to monitor tablespace usage - oracle database script

SET feedback OFF
SET pagesize 70;


SET linesize 2000
SET head ON COLUMN TABLESPACE format a25 heading 'Tablespace Name' COLUMN autoextensible format a11 heading 'AutoExtend' COLUMN files_in_tablespace format 999 heading 'Files' COLUMN total_tablespace_space format 99999999 heading 'TotalSpace' COLUMN total_used_space format 99999999 heading 'UsedSpace' COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace' COLUMN total_used_pct format 9999 heading '%Used' COLUMN total_free_pct format 9999 heading '%Free' COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto' COLUM total_auto_used_pct format 999.99 heading 'Max%Used' COLUMN total_auto_free_pct format 999.99 heading 'Max%Free' WITH tbs_auto AS
  (SELECT DISTINCT tablespace_name,
                   autoextensible
   FROM dba_data_files
   WHERE autoextensible = 'YES'),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        files AS
  (SELECT tablespace_name,
          COUNT (*) tbs_files,
                SUM (BYTES/1024/1024) total_tbs_bytes
   FROM dba_data_files
   GROUP BY tablespace_name),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        fragments AS
  (SELECT tablespace_name,
          COUNT (*) tbs_fragments,
                SUM (BYTES)/1024/1024 total_tbs_free_bytes,
                    MAX (BYTES)/1024/1024 max_free_chunk_bytes
   FROM dba_free_space
   GROUP BY tablespace_name),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        AUTOEXTEND AS
  (SELECT tablespace_name,
          SUM (size_to_grow) total_growth_tbs
   FROM
     (SELECT tablespace_name,
             SUM (maxbytes)/1024/1024 size_to_grow
      FROM dba_data_files
      WHERE autoextensible = 'YES'
      GROUP BY tablespace_name
      UNION SELECT tablespace_name,
                   SUM (BYTES)/1024/1024 size_to_grow
      FROM dba_data_files
      WHERE autoextensible = 'NO'
      GROUP BY tablespace_name)
   GROUP BY tablespace_name)
SELECT c.instance_name,
       a.tablespace_name TABLESPACE,
                         CASE tbs_auto.autoextensible
                             WHEN 'YES' THEN 'YES'
                             ELSE 'NO'
                         END AS autoextensible,
                         files.tbs_files files_in_tablespace,
                         files.total_tbs_bytes total_tablespace_space,
                         (files.total_tbs_bytes - fragments.total_tbs_free_bytes) total_used_space,
                         fragments.total_tbs_free_bytes total_tablespace_free_space,
                         round((((files.total_tbs_bytes - fragments.total_tbs_free_bytes) / files.total_tbs_bytes) * 100)) total_used_pct,
                         round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100)) total_free_pct
FROM dba_tablespaces a,
     v$instance c,
     files,
     fragments,
     AUTOEXTEND,
     tbs_auto
WHERE a.tablespace_name = files.tablespace_name
  AND a.tablespace_name = fragments.tablespace_name
  AND a.tablespace_name = AUTOEXTEND.tablespace_name
  AND a.tablespace_name = tbs_auto.tablespace_name(+)
ORDER BY total_free_pct;