온라인 테이블 재정의(Online Table Redefinition)는 운영 환경의 Oracle® 테이블 구조를 데이터 접근성을 유지한 채 변경할 수 있는 강력한 기능입니다. 임시 테이블(temp table)을 이용해 데이터를 옮기는 방식에 익숙하다면, 더 나은 대안이 있다는 사실을 알아두세요.
소개
테이블 구조를 변경하는 동안 데이터를 스테이징하고 이동시키는 기존 방식에서는 일정 기간 테이블과 데이터 모두 사용할 수 없게 됩니다. 비즈니스 관점에서 이는 결코 바람직하지 않은 상황입니다. 바로 이럴 때 DBMS_REDEFINITION 패키지가 해결책이 되어 줍니다.
목적
다음과 같은 이유로 Oracle 테이블의 논리적 또는 물리적 구조를 주기적으로 수정해야 할 필요가 있습니다.
- 쿼리 또는 DML(Data Manipulation Language) 성능 향상
- 애플리케이션 변경 사항 수용
- 스토리지 관리
Oracle Database는 테이블 가용성에 큰 영향을 주지 않고 구조를 변경할 수 있는 메커니즘을 제공하며, 이를 온라인 테이블 재정의라고 합니다. 온라인 재정의는 기존의 전통적인 테이블 재구성 방식에 비해 상당한 성능 향상을 제공합니다.
테이블을 온라인으로 재정의하는 동안에는 프로세스 대부분의 시간 동안 쿼리와 DML 작업이 모두 가능합니다. 배타적(exclusive) 모드로 테이블이 잠기는 시간은 아주 짧으며, 그 시간은 테이블 크기나 재정의 작업의 복잡도와 무관합니다. 또한 재정의 과정은 사용자에게 완전히 투명하게 진행됩니다.
단, 온라인 테이블 재정의에는 재정의 대상 테이블이 현재 사용 중인 공간과 거의 같은 크기의 여유 공간이 필요하다는 점을 유의해야 합니다.
테이블을 재구성하는 방법은 여러 가지가 있지만, 다운타임이 허용되지 않는 환경이라면 DBMS_REDEFINITION 패키지가 가장 좋은 선택입니다.
온라인으로 테이블 재정의하는 절차
다음 단계에 따라 테이블을 온라인으로 재정의할 수 있습니다.
재정의 방식을 선택합니다.
by key(기본 키 기반) 또는by rowids(ROWID 기반) 중 하나를 고릅니다.By key(기본 키 방식): 재정의에 사용할 기본 키(primary key) 또는 의사 기본 키(pseudo-primary key)를 지정합니다. 의사 기본 키란 모든 구성 컬럼에
NOT NULL제약 조건이 있는 유니크 키를 말합니다. 이 방식에서는 재정의 전후 테이블이 동일한 기본 키 컬럼으로 구성되어야 합니다. 권장되는 기본(default) 방식입니다.By rowid(ROWID 방식): 사용할 수 있는 키가 없을 때 사용합니다. 이 방식에서는 재정의된 테이블에
M_ROW$$라는 숨김 컬럼이 추가됩니다. 재정의 완료 후 이 컬럼은 삭제하거나 UNUSED로 표시해야 합니다.COMPATIBLE파라미터가 10.2.0 이상이면 재정의 마지막 단계에서 자동으로 UNUSED 처리되며, 이후ALTER TABLE ... DROP UNUSED COLUMNS문으로 삭제할 수 있습니다. 단, 인덱스 구성 테이블(index-organized table)에는 이 방식을 사용할 수 없습니다.CAN_REDEF_TABLE프로시저를 호출하여 해당 테이블이 온라인 재정의 가능한지 확인합니다. 테이블이 온라인 재정의 대상이 아니라면, 그 이유를 알려주는 오류가 발생합니다.재정의 대상 테이블과 동일한 스키마에 원하는 논리적·물리적 속성을 모두 갖춘 빈 중간(interim) 테이블을 생성합니다.
중간 테이블에 인덱스, 제약 조건, 권한(grant), 트리거 등을 일일이 생성할 필요는 없습니다.
COPY_TABLE_DEPENDENTS프로시저를 사용하면 이를 자동으로 복사할 수 있습니다.대용량 테이블의 성능을 높이려면 다음 명령으로 병렬(parallel) 처리를 설정할 수 있습니다:
ALTER SESSION force parallel dml parallel degree-of-parallelism; ALTER SESSION force parallel query parallel degree-of-parallelism;FINISH_REDEF_TABLE명령으로 재정의를 완료합니다. 이 과정에서 원본 테이블은 아주 짧은 시간 동안 배타적 모드로 잠기며, 잠금 시간은 원본 테이블의 데이터 양과 무관합니다. 다만FINISH_REDEF_TABLE은 재정의를 완료하기 전에 대기 중인 모든 DML 작업의 커밋을 기다립니다.ROWID 방식으로 재정의했고
COMPATIBLE초기화 파라미터가 10.1.0 이하라면, 재정의된 테이블에 추가된 숨김 컬럼M_ROW$$를 직접 삭제해야 합니다. 다음 명령으로 컬럼을 UNUSED 상태로 설정할 수도 있습니다:ALTER TABLE <table_name> SET UNUSED (M_ROW$$);COMPATIBLE이 10.2.0 이상이면 재정의 완료 시 이 숨김 컬럼이 자동으로 UNUSED 처리되므로, 이후ALTER TABLE ... DROP UNUSED COLUMNS문으로 삭제하면 됩니다. 중간 테이블에 대해 실행 중인 장기 쿼리가 모두 끝날 때까지 기다린 후 중간 테이블을 삭제합니다.
실습 예제: 테이블 재정의
다음은 샘플 테이블 재정의 과정에서 사용되는 각종 명령과 출력 결과의 예입니다.
sqlplus 시작
다음은 sqlplus를 시작하는 예제입니다:
[oracle@vm215 ~]$ sqlplus amit/amit
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
데모 테이블 생성
다음은 AMIT 스키마 아래에 데모 테이블 TEST1을 생성하는 예제입니다.
SQL> CREATE TABLE TEST1 ( ID NUMBER(10) ,
ENAME VARCHAR2(10),
SAL NUMBER(10) ) ;
대량 행 삽입
다음은 대량의 행을 삽입하고 AMIT 스키마에서 집계 관련 파라미터를 최대값으로 설정하는 예제입니다.
SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM,
DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;
999999 ROWS CREATED.
SQL> COMMIT;
COMMIT COMPLETE.
테스트용 종속 객체 생성
다음은 테이블 TEST1과 연관된 종속 객체들을 생성하여, 온라인 재정의 과정에서 어떤 변화가 일어나는지 확인할 수 있게 하는 예제입니다.
뷰(View) 생성
SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;
VIEW CREATED.
시퀀스(Sequence) 생성
SQL> CREATE SEQUENCE TEST_SEQ ;
SEQUENCE CREATED.
프로시저(Procedure) 생성
CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER)
AS V_ID NUMBER ;
BEGIN
SELECT SAL
INTO V_ID
FROM TEST1
WHERE ID = P_ID;
END;
/
PROCEDURE CREATED.
DML 트리거(Trigger) 생성
SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG
BEFORE INSERT OR UPDATE ON TEST1
FOR EACH ROW
DECLARE
X NUMBER;
BEGIN
SELECT COUNT(*) INTO X
FROM TEST1
WHERE ID = :NEW.ID;
IF X > 0 THEN
RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');
END IF;
END;
/
TRIGGER CREATED.
기본 키 생성
SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;
TABLE ALTERED.
재정의 전 객체 상태 확인
SQL> COLUMN OBJECT_NAME FORMAT A20
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
AMIT_TRIG TRIGGER VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_ID_PK INDEX VALID
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
6 ROWS SELECTED.
테이블 재정의 가능 여부 확인
다음은 rowids 또는 primary key 방식으로 테이블을 온라인 재정의할 수 있는지 확인하는 예제입니다:
기본 키 방식 사용
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
ROWID 방식 사용
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
중간 테이블 복제본 생성
다음은 종속 객체 없이 새로운 중간(interim) 테이블 복제본을 생성하는 예제입니다:
SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ;
TABLE CREATED.
SQL> SELECT COUNT(*) FROM TEST1_REORG ;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
데이터베이스 접속
다음은 테이블 재정의 작업을 실행하기 위해 권한 있는 사용자로 접속하는 예제입니다:
[oracle@vm215 ~]$ sqlplus / as sysdba
Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016
Copyright (c) 1982, 2011, oracle. All rights reserved.
Connected to:
Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
With the partitioning, olap, data mining and real application testing options
재정의 시작
다음은 기본 키를 사용하여 재정의를 시작하는 예제입니다:
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
종속 객체 복사
다음은 mview, 기본 키, 뷰, 시퀀스, 트리거 등 종속 객체를 자동으로 복사하는 예제입니다. COPY_TABLE_DEPENDENTS 명령 실행 시 기본 키 위반 오류를 피하기 위해 IGNORE_ERROR를 TRUE로 설정합니다.
SQL> DECLARE
N PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
오류 확인
다음은 DBA_REDEFINITION_ERRORS 뷰에서 오류를 확인하는 예제입니다:
SQL> COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT
FROM DBA_REDEFINITION_ERRORS;
NO ROWS SELECTED
양쪽 테이블 검증
다음은 두 테이블의 행 수를 검증하고 중간 테이블과 동기화하는 예제입니다:
SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ;
COUNT(*)
----------
999999
SQL> SELECT COUNT(*) FROM AMIT.TEST1 ;
COUNT(*)
----------
999999
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
재정의 완료
다음은 재정의를 마무리하는 예제입니다:
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------- ------------------- -------
TEST1_VW VIEW INVALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER INVALID
AMIT_TRIG TRIGGER INVALID
9 ROWS SELECTED.
오류 확인 및 스키마 재컴파일
다음은 전체 종속성을 반영하여 스키마를 재컴파일하는 예제입니다. 앞 단계에서 INVALID 상태가 된 트리거들이 있으므로 이 과정이 필요합니다:
SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
---------------------------------------- ------------------- -------
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER VALID
AMIT_TRIG TRIGGER VALID
9 ROWS SELECTED.
중간 테이블 삭제
다음은 중간 테이블을 삭제하는 예제입니다:
SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.
결론
테이블 구조를 변경해야 하는 동시에 최종 사용자의 접근도 계속 허용해야 한다면 DBMS_REDEFINITION 패키지를 활용하세요.
이 기능은 다운타임 없이 데이터를 재구성할 수 있게 해주므로, OLTP(온라인 트랜잭션 처리) 환경에서 다운타임으로 인한 고객 불편 문제를 효과적으로 해결해 줍니다.