[Oracle][Admin] SQL을 활용하여 오라클 데이터 파일 축소 작업

[테스트 환경]

OS : windows server 2008 

DB : ORACLE 11gR2

오라클 데이터 파일(Data file)은 데이터가 적재 될수록 크기가 점점 늘어나며, 데이터를 삭제 또는 테이블을 DROP 한다고해서 크기가 줄어 들지 않는다. 따라서 데이터 파일의 현재 사용량을 계산하고 RESIZE작업을 해야만 데이터 파일 크기를 변경 할수 있다.  SQL을 통하여 현재 데이터 파일의 사용량을 확인하고, 축소 가능한 데이터 파일 용량을 계산하여 최종 변경하는  출력하는 작업을 진행 한다. 

1 .현재 데이터 파일 사용량 확인

   - 테이블 스페이스 명으로 조회하여 몇 개의 데이터 파일로 구성된 개수와 데이터 파일의 크기 및 사용량을 확인 한다.

[조회 쿼리]
SELECT   SUBSTR(A.TABLESPACE_NAME,1,30) AS TABLESPACE_NM, /*테이블스페이스 명 */
         (SELECT SQ1.FILE_NAME FROM DBA_DATA_FILES SQ1 WHERE SQ1.FILE_ID = A.FILE_ID)  AS FILE_NM , /*데이터 파일 경로 및 명*/
         ROUND(SUM(A.TOTAL)/1024/1024,1)  AS TotalMB,   /*물리적 전체 용량*/
         ROUND(SUM(A.TOTAL)/1024/1024,1)-ROUND(SUM(A.SUM)/1024/1024,1)  AS UsedMB, /*사용량*/
         ROUND(SUM(A.SUM)/1024/1024,1)  AS FreeMB, /*남은 량*/
         ROUND((ROUND(SUM(A.TOTAL)/1024/1024,1)-ROUND(SUM(A.SUM)/1024/1024,1))/ROUND(SUM(A.TOTAL)/1024/1024,1)*100,2) AS Used_Rate  /*사용량(%)*/
FROM     (SELECT  A.TABLESPACE_NAME,
                  A.FILE_ID,
                  0 TOTAL,
                  SUM(A.BYTES) SUM,
                  MAX(A.BYTES) MAXB,
                  COUNT(A.BYTES) CNT
          FROM    DBA_FREE_SPACE A
          GROUP BY A.TABLESPACE_NAME,A.FILE_ID
          UNION
          SELECT   B.TABLESPACE_NAME,
                   B.FILE_ID,
                   SUM(B.BYTES) TOTAL,0,0,0
          FROM     DBA_DATA_FILES B
          GROUP BY B.TABLESPACE_NAME,B.FILE_ID) A
WHERE     A.TABLESPACE_NAME ='DA_DATA'   /*[변수]테이블스페이스 명*/       
GROUP BY  A.TABLESPACE_NAME,A.FILE_ID;

2. 테이블 삭제를 통한 공간 확보 작업

2.1. 테이블 삭제 작업 

  - RESIZE하려는 데이터 파일을 사용하는 테이블 삭제 

  - 테이블 DROP을 통해 사용 공간 반환 

  - 테이블 DROP 할 경우, 자동적으로 세그먼트를 테이블스페이스에 반환하여 별도의 작업 없이 데이터 파일 축소 작업만 진행 하면 된다.

[명령어]
/* 테이블 완전 삭제 */
DROP TABLE TB_DA_0001 PURGE;

3. 데이터 파일 축소 작업

3.1. 축소하려는 테이블스페이스의 데이터 파일 조회 

   - 아래 쿼리는 SYSTEM계정 또는 SYS 계정으로 실행해야 한다.

   - 테이블스페이스 명만 입력하면 데이터 파일 별로 축소 가능한 크기가 계산 되어 변경 문법이 출력 된다.

[조회쿼리]    
SELECT  A.TABLESPACE_NAME, /*테이블스페이스 명*/
        A.FILE_ID, /*파일ID*/
        'ALTER DATABASE DATAFILE '''||A.FILE_NAME||''' RESIZE ' || CEIL((A.BLOCK_ID*A.DB_BLOCK_SIZE + A.BLOCKS*A.DB_BLOCK_SIZE)/1024/1024)||'M;' AS RESZIE /*데이터 파일 RESIZE 명령어*/
FROM    (SELECT   A.FILE_ID,
                  A.BLOCK_ID,
                  A.BLOCKS,
                  A.FILE_NAME,
                  A.TABLESPACE_NAME,
                  (SELECT    A.VALUE  /*블록 사이즈*/
                   FROM     V$PARAMETER A
                   WHERE    A.NAME ='db_block_size') AS DB_BLOCK_SIZE
         FROM    (SELECT   A.FILE_ID,
                           A.BLOCK_ID, /*블록ID*/
                           A.BLOCKS,  /*블록SIZE*/
                           B.FILE_NAME, /*데이터 파일명*/
                           B.TABLESPACE_NAME,
                           RANK() OVER(PARTITION BY A.FILE_ID ORDER BY A.BLOCK_ID DESC) AS RN
                  FROM     DBA_EXTENTS A, 
                             DBA_DATA_FILES B 
                  WHERE    A.FILE_ID = B.FILE_ID
                  AND      A.TABLESPACE_NAME = 'DA_DATA' /*[변수] 테이블 스페이스 명*/) A
         WHERE   A.RN = 1 ) A;

4. 참고 사항 

  - 산정 된 용량보다 작은 사이즈로 RESIZE를 하면 아래와 같은 에러가 발생 한다.

  - 에러 코드: ORA-03297: file contains used data beyond requested RESIZE value

  - 이유: 산정된 용량은 HWM(HIGH WATER MARK)지점을 계산한 것이며, 용량 이하는 데이터가 존재하고 있기 때문에 RESIZE 할 수 가 없다.