shrink 대상 선정 및 shrink작업

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

 

1) Chaining이 있는 테이블 select

 

select owner, table_name, num_rows, blocks, empty_blocks, chain_cnt,
to_char(100*(chain_cnt/num_rows),'999')||'%' as chain_pct from dba_tables
where owner not in ('SYS','SYSTEM','TRACESVR','DBSNMP','SCOTT')
  and num_rows !=0 and chain_cnt !=0;