[Oracle] 테이블 스페이스 사용량 조회

SELECT A.TABLESPACE_NAME AS "테이블스페이스명"

     , A.BYTES / 1024  AS "전체(MB)"

     , (A.BYTES - NVL(B.FREEBYTES,0)) / 1024 AS "사용량(MB)"

     , NVL(B.FREEBYTES,0) / 1024 AS "잔여량(MB)"

     , ROUND(NVL(B.FREEBYTES,0)*100/A.BYTES,2) AS "%"

FROM (

      SELECT TABLESPACE_NAME

           , SUM(BYTES) BYTES 

        FROM DBA_DATA_FILES 

       GROUP BY TABLESPACE_NAME

      ) A

   , (

      SELECT TABLESPACE_NAME

           , SUM(NVL(BYTES,0)) FREEBYTES 

        FROM DBA_FREE_SPACE 

       GROUP BY TABLESPACE_NAME

      ) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

ORDER BY 5 ASC;

=========================================================================

select   substr(a.tablespace_name,1,30) tablespace,

         round(sum(a.total1)/1024/1024,1) "TotalMB",

         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

         round(sum(a.sum1)/1024/1024,1) "FreeMB",

         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

from

         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

          from     dba_free_space

          group by tablespace_name

          union

          select   tablespace_name,sum(bytes) total1,0,0,0

          from     dba_data_files

          group by tablespace_name) a

group by a.tablespace_name

order by tablespace;