set verify off linesize 180
col tablespace_name format a14
SELECT
"Check Time"
,TS_INFO.tablespace_name
,TS_INFO."TOTAL SIZE(MB)"
,TS_INFO."USED(MB)"
,TS_INFO."AVAIL(MB)" "Tot_AVAIL(MB)"
,TO_CHAR((MAX(DFS.BYTES)/1024/1024),'999,990.999') "Act_AVAIL(MB)"  -- max Extent size on The tablespace
,TS_INFO."USED %"
,TS_INFO."FREE %"
,TS_INFO."STATUS"
FROM (
SELECT
SYSDATE "Check Time"
,tablespace_name
,sum(total_size)/1024 "TOTAL SIZE(MB)"
,TO_CHAR(round((sum(used)/1024),4),'999,990.999') "USED(MB)"
,TO_CHAR((sum(total_size)/1024)-(round((sum(used)/1024),4)),'999,990.999') "AVAIL(MB)"
,TO_CHAR(round((sum(used)/sum(total_size)),4)*100,'990.99') "USED %"
,TO_CHAR(round(1-(sum(used)/sum(total_size)),4)*100,'909.99') "FREE %"
,CASE WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '10' THEN ' Alert'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '15' THEN ' Warning'
      WHEN  (round(1-(sum(used)/sum(total_size)),4)*100) <= '30' THEN ' Normal'
      ELSE ' Good' END "STATUS"
FROM (
SELECT
                  b.tablespace_name , -- tablespace Name
                  b.bytes / 1024 total_size, -- ÃÑ Bytes
                  ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 used, -- »ç¿ëÇÑ ¿ë·®
                  (sum(nvl(a.bytes,0))) / 1024 free_size -- ³²Àº ¿ë·®
                  FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
AND
b.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces)
                  GROUP BY b.tablespace_name, b.file_name, b.bytes
)
GROUP BY tablespace_name
) TS_INFO,
DBA_FREE_SPACE DFS 
WHERE TS_INFO.TABLESPACE_NAME = DFS.TABLESPACE_NAME
GROUP BY TS_INFO.tablespace_name,"TOTAL SIZE(MB)","USED(MB)","AVAIL(MB)","USED %","FREE %","STATUS","Check Time"
ORDER BY "FREE %";