dbms_redefinition 패키지 테스트 시나리오

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

설명 : 

데이터베이스 관리자는 DB에서 DML 작업과 같은 변경 작업이 많은 테이블에 대해서 블록 Row Chainning/Migration 또는 블록 단편화로 인한 성능 저하 및 비효율 적인 공간을 사용 중인 테이블에 대해서 Table Reorg를 수행한다.

일반적으로 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. 임시테이블 생성 - 리오그 전후를 비교하기 위해 파티션 테이블로 구성

SQL> CREATE TABLE SCOTT.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> col table_name for a10

SQL> col partition_name for a10

SQL> select table_name, partition_name from dba_tab_partitions where table_name='EMP';

TABLE_NAME                     PARTITION_NAME

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

EMP                            PR_1000

EMP                            PR_MAX

-- Online Reorg는 완료됐으며 다른 세션에서의 DML문은 정상수행 중이다. 

SQL> select count(*) from SCOTT.emp;

  COUNT(*)

----------

      661

참조 : http://m.dbguide.net/about.db?cmd=view&boardConfigUid=19&boardUid=184657