(9i) DBMS_REDEFINITION (COLUMN RENAME)
======================================

PURPOSE
--------

Table을 online상으로 reorganization할 수 있는 있는 DBMS_REDEFINITION package가
Oracle9i에서 소개되었다. 이 package을 이용하면 column rename이나 table상의
column의 위치를 바꾸고자 할때 유용하다.

Explanation
------------

1. DBMS_REDEFINITION

DBMS_REDEFINITION을 가지고 table의 reorganization을 online상에서 수행할 수
있다. 이 online reorganization을 수행하기 위해서는 materialized view가
사용되어진다. Snapshot log가 master table에 define되어지며 이 log가
online reorganization이 수행되는 동안에 master table에 변화된 내용을 반
영할수 있게 해준다.

< 제약사항 >
- Online reorganization을 수행하기 위해서는 primary key을 반드시 가져야 한다.
- Materialized view와 materialized view log가 define되어 있는 table은
online reorganization을 수행할 수 없다.
- IOT table의 overflow table에 대해서는 Online reorganization을 수행할
수 없다.

지금부터는 DBMS_REDEFINITION Package의 각각의 Subprogram에 대해 알아보자.

1) CAN_REDEF_TABLE Procedure:

주어진 table이 online reorganization을 할 수 있는지 없는지를 결정하는
procedure이다. Online reorganization 작업의 첫번째 순서가 된다. 만약 table이
online redefinition에 적합하지 않다면 예제에서도 다루지만 'ORA-12089' error
message를 낸다.

DBMS_REDEFINITION.can_redef_table (
uname IN VARCHAR2,
tname IN VARCHAR2);

CAN_REDEF_TABLE Procedure Parameters:

Parameter Description
--------- ------------
uname table의 schema(user) name.
tname reorganization할 table의 name.

2) START_REDEF_TABLE Procedure:

Reorganization 작업을 초기화하는 procedure이다. Online reorganization을 할 수
있는지를 알아본 이후에 희망하는 reorganization 이후의 모습을 가진 빈 공간의 임시
table을 생성한다.

DBMS_REDEFINITION.start_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
col_mapping IN VARCHAR2 := NULL);

START_REDEF_TABLE Procedure Parameters:

Parameter Description
---------- ------------
uname table의 schema(user) name.
orig_table reorganization할 table의 name.
int_table 임시 table의 이름.
col_mapping 임시 table과 Original table과의 mapping정보
(Select 절에서의 column list와 비슷하다) 만약
NULL이면 original table의 전체 column이 선택된
것이 되며 reorganization이후에도 같은 column 이름을
가지게 된다.

3) FINISH_REDEF_TABLE Procedure:

Reorganization 작업을 완성하는 procedure이다. 필요하다면 이 순서전에 새로운
index, trigger, grant, constraint등을 임시 table에 생성한다.
Referential
constraint는 임시 table이 disable된 이후에 포함한다. 이 작업이 완성될때까지는
orginal table에 일시적으로 lock이 걸리게 된다.

DBMS_REDEFINITION.finish_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

FINISH_REDEF_TABLE Procedure Parameters:

Parameter Description
--------- ------------
uname table의 schema(user) name.
orig_table reorganization할 table의 name.
int_table 임시 table의 이름.

4) SYNC_INTERIM_TABLE Procedure:

Original table과 임시 table을 동기화시키는 procedure이다. 즉 Online
reorganization 작업중에 변경된 사항을 맞춰주는 작업이다.

DBMS_REDEFINITION.sync_interim_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

SYNC_INTERIM_TABLE Procedure Parameters:

Parameters Description
---------- ------------
uname table의 schema(user) name.
orig_table reorganization할 table의 name.
int_table 임시 table의 이름.


5) ABORT_REDEF_TABLE Procedure:

Reorganization 작업중에 error가 난 경우에 멈추기 위한 procedure이다.
이 procedure는 어느때나 호출할 수 있다.

DBMS_REDEFINITION.abort_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);

ABORT_REDEF_TABLE Procedure Parameters:

Parameters Description
---------- ------------
uname table의 schema(user) name.
orig_table reorganization할 table의 name.
int_table 임시 table의 이름.

Example
--------

아래의 emp table은 아무런 constraint을 가지고 있지 않다고 가정한다.
예로 JOB 이라는 column name을 DESIGNATION 으로 바꾸고 EMPNO에 의해 partition
되도록 바꾸고자 한다.

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


1. SCOTT user에게 DBMS_REDEFINITION을 실행할수 있도록 권한을 부여한다.

SQL> connect sys/manager as sysdba
Connected.
SQL> grant execute on dbms_redefinition to scott;

Grant succeeded.

2. Dbms_redefinition.can_redef_table procedure을 이용하여 redefine가능한지
결정한다.

SQL> connect scott/tiger
Connected.

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 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 236
ORA-06512: at line 1

primary key가 없다면 위와 같은 error가 발생하며 가능하다면 primary key을
가지도록 수정한다. 왜냐하면 이 online reorganization동안에 생성되는
Materialized view 와 log가 primary key가 꼭 있어야 하기 때문이다.

SQL> alter table emp add constraint emp_pk primary key (empno);

Table altered.

SQL> exec dbms_redefinition.can_redef_table('SCOTT', 'EMP');

PL/SQL procedure successfully completed.

3. 원하는 성격을 지닌 임시 table을 생성한다.
예로 JOB 이라는 column name을 DESIGNATION 으로 바꾸고 EMPNO에 의해
partition 되도록 바꾸고자 한다.

SQL> create table int_emp
(empno number primary key,
ename varchar2(10),
designation varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
Partition By Range (empno)
(partition emp_part1 values less than (2000),
partition emp_part2 values less than (maxvalue)
);

Table created.

4. Redefinition을 시작한다.

SQL> exec dbms_redefinition.start_redef_table('SCOTT','EMP','INT_EMP', -
'EMPNO EMPNO, ENAME ENAME, JOB DESIGNATION, MGR MGR, HIREDATE HIREDATE, -
SAL SAL,COMM COMM,DEPTNO DEPTNO');
PL/SQL procedure successfully completed.

5. Online reorganization 작업중에 변경된 사항을 맞춰주기 위해
sync_interim_table procedure을 호출한다. 이 작업을 함으로써
finish_redef_table시에 동기화를 맞춰주는 양을 줄일 수 있다.

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','EMP','INT_EMP');

PL/SQL procedure successfully completed.

6. 임시 table을 query하여 data가 잘 들어 왔는지 확인하고 finish하도록 한다.

SQL> select * from int_emp;

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','EMP','INT_EMP');

PL/SQL procedure successfully completed.

이 procedure을 call하고 나면 original table과 임시 table이 바뀌는 것을 볼수
있다. 즉 원하는 형태로 바뀌는 최종 단계이다

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(10)
DESIGNATION <-- 요기 column rename 완료 VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc int_emp
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

위와 같이 emp table이 원하는 형태로 바뀐 것을 볼 수 있다.


7. 그러면 이제는 임시 table인 int_emp table을 drop해도 무방하다.
항상 data가 제대로 다 옮겨졌는지를 확인하고 drop한다.

SQL> drop table int_emp;

Table dropped.


RELATED DOCUMENTS
-----------------

Note:149564.1
Note.157084.1
Oracle9i Database Administrator's Guide Part Number A90117-01
Oracle9i Supplied PL/SQL Packages and Types Reference Part Number A89852-02