Oracle Online re-org 기능을 이용해 일반 table을 interval partition으로 변경하는 방법

다음은 일반 테이블을 partition table로 online redefinition기능을 이용해 변경하는 예제입니다. 

SCOTT.EMP_REDEF table은 HIREDATE가 varchar2로 정의되어 있는데, 

partition table을 day interval로 하기 위해서 HIREDATE를 date type으로 바꿔줘야 합니다. 

online redefinition 기능을 이용해서 HIREDATE column 값을 이용해 hir_date 라는 date column을 추가하고 

partition으로 변경합니다.

1. table 생성 및 데이터 입력

CREATE TABLE SCOTT.EMP_REDEF

(EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE varchar2(8),

SAL NUMBER(7, 2),

COMM NUMBER(7, 2),

DEPTNO NUMBER(2)) tablespace USERS;

alter table SCOTT.emp_REDEF add constraint emp_pk primary key (empno);

INSERT INTO SCOTT.EMP_REDEF VALUES (7369, 'SMITH', 'CLERK', 7902,'20100415', 800, NULL, 20);

commit;

2. redefinition 가능 여부 판단. 

exec dbms_redefinition.can_redef_table('SCOTT', 'EMP_REDEF');

3. 임시 table 생성 

create table scott.emp_REDEF2

    (empno number primary key,

    ename varchar2(10),

    designation varchar2(9),

    mgr number(4),

    hiredate varchar2(8),

    hir_date date,

    sal number(7,2),

    comm number(7,2),

    deptno number(2)

    ) 

partition by range (hir_date) interval (numtodsinterval(1,'DAY'))

( partition p_before_1_jan_2005 values  less than (to_date('20050101','yyyymmdd')))

tablespace USERS

/

4. redefinition 지정.

alter session set nls_date_format = 'yyyymmdd';

exec dbms_redefinition.start_redef_table('SCOTT','EMP_REDEF','EMP_REDEF2', 'EMPNO EMPNO, ENAME ENAME, JOB DESIGNATION, MGR MGR, HIREDATE HIREDATE, to_date(hiredate) hir_date, SAL SAL,COMM COMM,DEPTNO DEPTNO');

5. data sync

exec dbms_redefinition.sync_interim_table('SCOTT','EMP_REDEF','EMP_REDEF2');

6. finish 

exec dbms_redefinition.finish_redef_table('SCOTT','EMP_REDEF','EMP_REDEF2');

7. 임시 table 삭제. 

drop table scott.emp_REDEF2;

8. 원본 데이터 변경 확인. 

SQL> select * from scott.emp_REDEF;

     EMPNO ENAME                          DESIGNATION                        MGR HIREDATE                 HIR_DATE        SAL       COMM     DEPTNO

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

      7369 SMITH                          CLERK                             7902 20100415                 20100415        800                    20

SQL> desc scott.emp_REDEF

 Name                                                                                                              Null?    Type

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

 EMPNO                                                                                                             NOT NULL NUMBER

 ENAME                                                                                                                      VARCHAR2(10)

 DESIGNATION                                                                                                                VARCHAR2(9)

 MGR                                                                                                                        NUMBER(4)

 HIREDATE                                                                                                                   VARCHAR2(8)

 HIR_DATE                                                                                                                   DATE

 SAL                                                                                                                        NUMBER(7,2)

 COMM                                                                                                                       NUMBER(7,2)

 DEPTNO                                                                                                                     NUMBER(2)

SQL> select table_name,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name = 'EMP_REDEF';

TABLE_NAME                                                                                 PARTITION_NAME

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

HIGH_VALUE

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

EMP_REDEF                                                                                  P_BEFORE_1_JAN_2005

TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

EMP_REDEF                                                                                  SYS_P41

TO_DATE(' 2010-04-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

참고:  Partitioning a table online with DBMS_REDEFINITION

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!