set lines 200
set pages 200
col file_name for a50
col tablespace_name for a30
col status for a21
compute sum of "Total(Mb)" on report
compute sum of "Free(Mb)" on report
break on report
SELECT  t.tablespace_name,
        ts.contents,
        ts.status,
        round(nvl(t.bytes,0)/1024/1024,1) "Total(Mb)",
        round((nvl(nvl(f.free,ft.free),0)/1024/1024),1) "Free(Mb)",
        round((nvl(nvl(f.free,ft.free),0)*100/t.bytes),1) "% Free",
        decode((case when round((nvl(nvl(f.free,ft.free),0)/1024/1024/1024))>=5 then 'OK' else 'NOK' end),'OK','OK',decode(contents,'UNDO','OK - UNDO TABLESPACE',decode(contents,'TEMPORARY','OK - TEMP TABLESPACE',decode(round((nvl(nvl(f.free,ft.free),0)*100)/t.bytes) ,'0','CRITICAL','1','CRITICAL','2','CRITICAL','3','CRITICAL','4','CRITICAL','5','WARNING','6','WARNING','7','WARNING','8','WARNING','9','WARNING','OK'))))
         STATUS
FROM                    (SELECT d.tablespace_name,
                                sum(d.bytes) bytes
                        FROM    dba_data_files d
                        GROUP BY tablespace_name
                        UNION
                        SELECT  d.tablespace_name,
                                sum(d.bytes) bytes
                        FROM    dba_TEMP_files d
                        GROUP BY tablespace_name) t,
                        (SELECT tablespace_name,
                                sum(bytes) free
                        FROM    dba_free_space
                        GROUP BY tablespace_name) f,
                        (select TABLESPACE_NAME,
                                sum(bytes_free) free
                        from    V$TEMP_SPACE_HEADER
                        group by tablespace_name) ft,
                        dba_tablespaces ts
WHERE   t.tablespace_name = f.tablespace_name(+)
AND     t.tablespace_name = ft.tablespace_name(+)
AND     t.tablespace_name = ts.tablespace_name
ORDER BY 5;