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 %";
김상백 
