[오라클 Oracle] 테이블 스페이스 용량 확인

* 테이블 스페이스의 용량이 다 찰 경우, 해당 테이블 스페이스를 참조하고 있는 테이블들이 정상적으로 I/O가 일어날 수 없으므로 장애로 연결된다.

 

1. 전체 테이블 스페이스 확인(TEMP_USED 미포함)

SELECT A.TABLESPACE_NAME,
	   ROUND(SUM(A.BYTES) / (1024 * 1024 * 1024)) "전체(GB)",
       ROUND(SUM(B.FREES) / (1024 * 1024 * 1024)) "여유(GB)",
       ROUND(SUM(B.FREES) * 100 / SUM(A.BYTES), 2) AS "잔여공간비율(%)"
  FROM (SELECT FILE_ID,
    	 	   TABLESPACE_NAME,
               SUM(BYTES) BYTES
          FROM DBA_DATA_FILES
         GROUP BY FILE_ID, TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME,
               FILE_ID,
               SUM(BYTES) FREES
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME, FILE_ID) B
 WHERE A.TABLESPAC_NAME = B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
 GROUP BY A.TABLESPACE_NAME
 ;

 

2. 전체 테이블 스페이스 확인(TEMP_USED 포함)

SELECT * 
  FROM (SELECT D.STATUS AS "STATUS",
  			   D.TABLESPACE_NAME AS "NAME",
               D.CONTENTS AS "TYPE",
               TRUNC(NVL(A.BYTES/1024/1024/1024, 0), 2) AS "SIZE(GB)",
               TRUNC(NVL(A.BYTES - NVL(F.BYTES, 0), 0)/1024/1024/1024, 2) AS "USED(GB)",
               TRUNC(NVL((A.BYTES - NVL(F.BYTES, 0))/A.BYTES * 100, 0), 2) AS "USED(%)"
          FROM SYS.DBA_TABLESPACES D,
               (SELECT TABLESPACE_NAME,
                       SUM(BYTES) BYTES
                  FROM DBA_DATA_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME,
                       SUM(BYTES) BYTES
                  FROM DBA_FREE_SPACE
                 GROUP BY TABLESPACE_NAME) F
         WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
           AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
           AND NOT (D.EXTENT_MANAGEMENT LIKE 'LOCAL'
                   AND D.CONTENTS LIKE 'TEMPORARY')
         UNION ALL    
        SELECT D.STATUS AS "STATUS",
               D.TABLESPACE_NAME AS "NAME",
               D.CONTENTS AS "TYPE",
               TRUNC(NVL(A.BYTES/1024/1024/1024, 0), 2) AS "SIZE(GB)",
               TRUNC(NVL(T.BYTES, 0)/1024/1024/1024, 2) AS "USED(GB)",
               TRUNC(NVL(T.BYTES / A.BYTES * 100, 0), 2) AS "USED(%)"
          FROM SYS.DBA_TABLESPACES D,
               (SELECT TABLESPACE_NAME,
               		   SUM(BYTES) BYTES
                  FROM DBA_TMP_FILES
                 GROUP BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME,
                  	   SUM(BYTES_USED) BYTES
                  FROM GV$TEMP_EXTENT_POOL
                 GROUP BY TABLESPACE_NAME) T
         WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
           AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
           AND D.EXTENT_MANAGEMENT LIKE 'LOCAL'
           AND D.CONTENTS LIKE 'TEMPORARY')
 ORDER BY "USED(%)" DESC;

 

3. 특정 테이블 스페이스에 포함된 테이블 조회

SELECT OWNER,
       SEGMENT_NAME,
       SEGMENT_TYPE,
       BYTES/1024/1024/1024 AS SIZE_GB
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = 'HHE_DATA'
 ORDER BY BYTES DESC;