일반적으로 Table Reorg를 다음과 같은 방식으로 수행한다.

• - Table t1의 Copy 본인 Table t2를 생성한다.
• - Table t2에 t1 데이터를 Insert 한다.
• - Table t1을 rename(혹은 drop)하고 Table t2를 t1으로 rename 한다.

위 방법은 데이터의 정합성을 위해 Reorg 작업 중에 서비스 중지가 필요하다. 하지만 DBMS_REDEFINTION 패키지를 이용하면 온라인에서도 Reorg 작업을 할 수 있다.

패키지 내부 구현은 Mview를 이용한 방식을 사용한다. 작업은 3단계로 이뤄진다. 우선 초기 데이터를 적재한다. 이후 중간 변경 이력을 동기화(Sync)하는 작업을 수행한다. 마지막으로 원본 테이블과 Interim 테이블을 맞교환(Exchange)한다.

패키지 사용방법에 대해서 간단한 예제로 온라인 Reorg를 수행해보자.

• [리스트 1] 패키지 사용 방법 예제

• 
  
  -- 1. 임시테이블 생성 - 리오그 전후를 비교하기 위해 파티션 테이블로 구성
  SQL> CREATE TABLE EMP_REF
  (
      EMPNO    NUMBER not null primary key,
      ENAME    VARCHAR2(10),
      JOB        VARCHAR2(9),
      MGR       NUMBER(4),
      HIREDATE  DATE,
      SAL       NUMBER(7, 2),
      COMM    NUMBER(7,2),
      DEPTNO   NUMBER(2)
  )
  partition by range(EMPNO)
  (
      partition PR_1000 values less than(1000),
      partition PR_MAX values less than(maxvalue)
  );
  Table created.
  
  
  -- dbms_redefinition 패키지 사용가능 여부 확인
  SQL> exec dbms_redefinition.can_redef_table('SCOTT','EMP');
  BEGIN dbms_redefinition.can_redef_table('SCOTT','EMP'); END;
  
  *
  ERROR at line 1:
  ORA-12089: cannot online redefine table "SCOTT"."EMP" with no primary key
  ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
  ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
  ORA-06512: at line 1
  
  
  -- 위의 오류는 Primary key가 없기 때문에 오류 발생
  -- primary key를 추가하고 다시 수행하면 정상적으로 실행한다.
  
  SQL> alter table emp add constraint emp_empno_pk primary key(empno);
  Table altered.
  
  
  --다른 세션에서 DML 발생 --------------------------------
  SQL> conn scott/tiger
  SQL> begin
  for i in 1 .. 7000 loop
    insert into EMP values( i, 'TEST', 'DBA', 7839, sysdate, 100, 10, 10);
    commit;
    dbms_lock.sleep(1/10);
  end loop;
  end;
  --------------------------------------------------------------------------
  
  -- start_redef_table를이용하여임시테이블과원본테이블의컬럼에서매핑정보를제공
  -- NULL 처리할경우, 원본테이블에있는모든컬럼을선택하게되며재정의를마치면
      동일한이름을가져오게된다
  SQL> exec dbms_redefinition.start_redef_table('scott','emp','emp_ref','empno empno,
  ename ename, job job, mgr mgr, hiredate hiredate, sal sal, comm comm, deptno deptno');
  PL/SQL procedure successfully completed.
  
  
  -- 동기화작업 Finish_redef_table 프로시저 수행 전에 여러 번 수행해 동기화가 가능
  SQL> exec dbms_redefinition.sync_interim_table('SCOTT','emp','emp_ref');
  PL/SQL procedure successfully completed.
  
  
  -- Reorg 작업을 종료한다. 수행하는 동안 원본테이블은 잠시 LOCK이 걸린다.
  SQL> exec dbms_redefinition.finish_redef_table('SCOTT','emp','emp_ref');
  PL/SQL procedure successfully completed.
  
  
  -- Reorg 후 변경된 테이블 레이아웃 확인
  SQL> select table_name, partition_name from user_tab_partitions where table_name='EMP';
  
  TABLE_NAME                     PARTITION_NAME
  ------------------------------ ------------------------------
  EMP                            PR_1000
  EMP                            PR_MAX
  
  
  -- Online Reorg는 완료됐으며 다른 세션에서의 DML문은 정상수행 중이다.
  SQL> select count(*) from emp;
  
    COUNT(*)
  ----------
        2323
  
  

DBMS_DEFINITION 패키지는 매우 유용하고 편리한 패키지다. 하지만 DML 작업이 많은 테이블에 대해서는 온라인 모드에서 사용을 자제하는 것이 좋을 듯하다.

DBMS_DEFINITION 패키지는 내부적으로 Sync 작업 시 Mview를 사용하여 동기화를 시키기 때문에 부하가 발생할 것이며 그 만큼 DB Wait 도 증가할 것이며 혹은 기타 다른 문제가 발생할 가능성이 있다. 시스템 상황에 맞게 유용하게 사용하도록 하자.