TABLESPACE 사용량 조회

 

 

 

 

1. 테이블스페이스 사용량 조회

SELECT A.TABLESPACE_NAME 

             ,  ROUND( A.BYTES / 1024 / 1024 / 1024, 2)  AS "전체용량(GB)"
             ,  A.BYTES / 1024 / 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 ;

 

 

 

 

 

결과 예시

 

 

 

 

 

 

2. TableSpace 용량 확인 (GB 단위)

 

 

SELECT A.TABLESPACE_NAME,
       ROUND(MAXBYTES/1048576,2) "최대용량(MB)",
       ROUND(A.BYTES_ALLOC / 1024 / 1024  / 1024 , 2) "할당용량(GB)",
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024  / 1024 , 2) "여유용량(GB)",
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024  / 1024 , 2) "사용량(GB)",
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) "여유%",
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) "사용%"
FROM   ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES) BYTES_ALLOC,
                SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
         FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME) A,
       ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES)  BYTES_FREE
         FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
      UNION
      SELECT TABLESPACE_NAME,
             ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
             ROUND(SUM(BYTES_FREE) / 1048576,2),
             ROUND(SUM(BYTES_USED) / 1048576,2),
             ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
             100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
             ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
      FROM   SYS.V_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
ORDER BY 1;