Oracle SHRINK SPACE 오라클 조각모음

SHRINK SPACE는 오라클10g부터 지원하는 기능으로,

세그먼트의 데이터 조각모음(ONLINE Reorg. 효과) 기능.

 

-----------------------------------------------------

1. SHRINK 작업을 수행하기 위한 주요 환경 및 주의할 점

-----------------------------------------------------

 

  1) 오라클 10g 이상(Init.ora parameter 'Compatible' must be >=10.0)

   2) 세그먼트 관리방식이 반드시 ASSM(Auto Segment Space Managed) Tablespace이어야 함

   3) 약 20여건씩 INSERT/DELETE하고 COMMIT하는 방식으로 SHRINK함

   4) 개별 ROW 또는 데이터 BLOCK에 대한 LOCK(ENQUEUE)이 사용

   5) FBI(Function-Based Index)를 SHRINK하는 경우

      오류 발생하므로 FBI를 DROP한 다음 작업하고

      SHRINK작업이 완료된 후 다시 생성

   6) DML TRIGGER를 발생시키지 않음(ROWID based TRIGGER는 작업 전에 DISABLED 필요)

   7) DML 작업은 세그먼트 SHRINK 중 수행 가능하나, parallel DML을 수행될 수 없음 

   8) 세그먼트를 SHRINK 시키는 특정 단계(HWM을 조정하는 단계)에서 세그먼트에

      exclusive 모드로 짧은 시간동안 LOCK(TM)이 걸림

 

-----------------------------------------------------

2. SHRINK 작업 방법 (순서 1]~6])

-----------------------------------------------------

 

  1] SHRINK 대상 테이블 분석 및 조회

      (NUM_ROWS에 비해 사용하는 BLOCKS가 과다하면 SHRINK필요)

      SQL> connect / as sysdba

      SQL> EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('소유자', 'COMPUTE');

      SQL> SELECT OWNER,     TABLE_NAME,   NUM_ROWS,

                  BLOCKS,    EMPTY_BLOCKS, AVG_SPACE,

                  CHAIN_CNT, AVG_ROW_LEN

             FROM DBA_TABLES

            WHERE OWNER = '소유자'

              AND EMPTY_BLOCKS / (BLOCKS + EMPTY_BLOCKS) < 0.1

              AND (BLOCKS + EMPTY_BLOCKS) > 0

            ORDER BY BLOCKS DESC;

 

      [참고] DBMS_SPACE.VERIFY_SHRINK_CANDIDATE

 

   2] 대상 테이블 및 관련 인덱스 NOLOGGING 설정

      SQL> connect 소유자/암호

      SQL> ALTER TABLE 테이블명 NOLOGGING;

      SQL> ALTER INDEX 인덱스명 NOLOGGING;

 

   3] 대상 테이블의 ROW MOVEMENT 기능 활성화

      (데이터의 조각모음으로 ROWID가 변경되므로 ENABLE ROW MOVEMENT)

      SQL> ALTER TABLE 테이블명 ENABLE ROW MOVEMENT;

 

   4] 필요한 작업을 선택적으로 작업

      4-1) 테이블만 SHRINK하고 HWM(High Water Mark)는 SHRINK하지 않음

           SQL> ALTER TABLE 테이블명 SHRINK SPACE COMPACT;

 

      4-2) 테이블과 HWM(High Water Mark)를 SHRINK

           (HWM SHRINK 시 TM 락 발생)

           SQL> ALTER TABLE 테이블명 SHRINK SPACE;

 

      4-3) 테이블과 테이블의 HWM을 SHRINK

           SQL> ALTER TABLE 테이블명 SHRINK SPACE COMPACT;

           SQL> ALTER TABLE 테이블명 SHRINK SPACE;

 

      4-4) 인덱스와 인덱스의 HWM을 SHRINK

           (ROWID가 변경되지 않으므로 ENABLE ROW MOVEMENT 불필요)

           SQL> ALTER INDEX 인덱스명 SHRINK SPACE COMPACT;

           SQL> ALTER INDEX 인덱스명 SHRINK SPACE;

 

      4-5) 테이블 및 관련된 인덱스를 모두 SHRINK

           SQL> ALTER TABLE 테이블명 SHRINK SPACE CASCADE COMPACT;

           SQL> ALTER TABLE 테이블명 SHRINK SPACE CASCADE;

 

   5] Row movement 비활성화

      SQL> ALTER TABLE 테이블명 DISABLE ROW MOVEMENT;

 

   6] 대상 테이블 및 관련 인덱스 LOGGING 설정

      SQL> ALTER TABLE 테이블명 LOGGING;

      SQL> ALTER INDEX 인덱스명 LOGGING;

 

   ※ M-View 형태의 테이블을 SHRINK

      on-commit materialized view와 연관된 세그먼트는 SHRINK 시킬 수 없음

      rowid에 기반을 둔 materialized view에 대해서는 refresh 또는 rebuild를 수행 필요

      SQL> ALTER TABLE M-View명 SHRINK SPACE COMPACT;

      SQL> ALTER TABLE M-View명 SHRINK SPACE;

[출처] 스키마관리-오라클 SHRINK SPACE|작성자 smileDBA

      ALTER TABLE 테이블명 NOLOGGING;

      

      ALTER INDEX 인덱스명 NOLOGGING;

      

      ALTER TABLE 테이블명 ENABLE ROW MOVEMENT;

      

      ALTER TABLE 테이블명 SHRINK SPACE CASCADE;

      

      

      ALTER TABLE 테이블명 DISABLE ROW MOVEMENT;

      

      ALTER TABLE 테이블명 LOGGING;

      

      ALTER INDEX 인덱스명 LOGGING;

파티션테이블  SHRINK 방법

ALTER TABLE EMP_PART ENABLE ROW MOVEMENT; 

ALTER TABLE EMP_PART MODIFY PARTITION EMP_PART_1 SHRINK SPACE CASCADE; 

ALTER TABLE EMP_PART DISABLE ROW MOVEMENT;