SQL to check space issues

The below SQL reports on the space usage by table space along with details on each data file. Additionally, it identifies if a physical standby exists.

 
SELECT
                 (
                  select    case when count(*) > 0 then 'Standby exists'
                                 else                   'No standby'
                            end                                          
                  from      gv$parameter 
                  where     lower(name) like 'log_archive_dest_%'
                    and not lower(name) like '%_state_%'
                    and not lower(display_value) like 'location=%'
                    and not value is null
                 )                                                       AS "STANDBY_STATUS"
                 ,'TABLE SPACE'                                          AS "OBJECT_TYPE"
                 ,DF.TABLESPACE_NAME                                     AS "OBJECT_NAME"
                 ,df.AUTOEXTENSIBLE                                      AS "AUTO_EXTEND"
                 ,' '                                                    AS "file_name"
                 ,0                                                      AS "FILE_ID"
                 ,ROUND(SUM((DF.BYTES/1024)/1024),2)                     AS "MB_ALLOC"
                 ,ROUND(SUM((FS.FREE_BYTES/1024)/1024),2)                AS "MB_FREE"
                 ,ROUND(((SUM(FS.FREE_BYTES)/SUM(DF.BYTES)) * 100),2)    AS "PCT_FREE"
                 ,case 
                       when ROUND(((SUM(FS.FREE_BYTES)/SUM(DF.BYTES)) * 100),2) < 10 then '<--Problem'
                       else                                                               '          '
                  end as "Check_flag" 
                 ,'B'                                                    AS "SORT_COL_01"
FROM              DBA_DATA_FILES    DF
left outer JOIN  (SELECT      FILE_ID
                             ,SUM(BYTES)                                 AS FREE_BYTES
                  FROM        DBA_FREE_SPACE
                  GROUP BY  FILE_ID) FS
             ON   DF.FILE_ID = FS.FILE_ID
GROUP BY          DF.TABLESPACE_NAME
                 ,df.AUTOEXTENSIBLE
UNION ALL
SELECT
                  ' '
                 ,'FILE        '
                 ,DF.TABLESPACE_NAME
                 ,' '
                 ,DF.FILE_NAME
                 ,DF.FILE_ID
                 ,round(((DF.BYTES/1024)/1024),2)
                 ,round(((FS.FREE_BYTES/1024)/1024),2)
                 ,round(((FS.FREE_BYTES/DF.BYTES) * 100),2)
                 ,' '
                 ,'C'
FROM              DBA_DATA_FILES    DF
left outer JOIN  (SELECT      FILE_ID
                             ,SUM(BYTES)                                 AS FREE_BYTES
                  FROM        DBA_FREE_SPACE
                  GROUP BY  FILE_ID) FS
             ON   DF.FILE_ID = FS.FILE_ID
ORDER BY          OBJECT_NAME
                 ,SORT_COL_01
                 ,"FILE_ID"
;