dbms_redefinition 패키지 (online 재생성 및 파티션 전환)

dbms_redefinition 패키지

reorg_TST_CM_VOC_EMAIL.SQL

DBMS_REDEFINITION은 온라인 상태에서 특정 테이블을 재정의 하기 위한 프로시저를 제공하는 패키지이다.

재정의할 대상인 원본 테이블을 지정된 컬럼 대응 정보에 따라 새 테이블로 재정의할 수 있다. 

새 테이블은 재정의 수행 이전에 미리 생성되어 있어야 하며, 

재정의가 끝나면 원본 테이블의 구조와 새 테이블의 구조가 서로 바뀐다.

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

1. ABORT_REDEF_TABLE

온라인 테이블 재정의를 취소하는 프로시저이다. 이 프로시저에 원본 테이블과 새 테이블의 이름을 지정하면, 온라인 테이블을 재정의하기 위해 생성한 내부 객체는 제거되고 재정의가 취소된다. 이때 원본 테이블과 새 테이블은 취소 시점의 상태로 남는다.

ABORT_REDEF_TABLE 프로시저의 세부 내용은 다음과 같다.

• 프로토타입

  PROCEDURE ABORT_REDEF_TABLE
  (
     uname        IN  VARCHAR2,
     orig_table   IN  VARCHAR2,
     int_table    IN  VARCHAR2
  );

• 파라미터

  파라미터	설명
  uname	원본 테이블과 새 테이블이 속한 스키마의 이름이다.
  orig_table	원본 테이블의 이름이다.
  int_table	새 테이블의 이름이다.

• 예제

  BEGIN
    DBMS_REDEFINITION.ABORT_REDEF_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE');
  END;
  /

3. COPY_TABLE_DEPENDENTS

테이블을 온라인 재정의를 수행하면서, 테이블과 관련된 다른 객체들도 복사해주는 프러시저이다. 

인덱스, 제약사항, 트리거, 권한 등에 대해서 기존 테이블의 관련 객체를 새 테이블에 복사해 줄지 파라미터로 명시 할 수 있다.

COPY_TABLE_DEPENDENTS 프러시저의 세부 내용은 다음과 같다.

. 프로토타입

PROCEDURE COPY_TABLE_DEPENDENTS 
( 
    uname IN VARCHAR2, 
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2,
    copy_indexes IN PLS_INTEGER := 1,
    copy_triggers IN BOOLEAN := TRUE,
    copy_constraints IN BOOLEAN := TRUE,
    copy_privileges IN BOOLEAN := TRUE, 
    ignore_errors IN BOOLEAN := FALSE,
    num_errors OUT PLS_INTEGER,
    copy_statistics IN BOOLEAN := FALSE,
    copy_mvlog IN BOOLEAN := FALSE
);

• 파라미터

• 파라미터	설명
  uname	원본 테이블과 새 테이블이 속한 스키마의 이름이다.
  orig_table	원본 테이블의 이름이다.
  int_table	새 테이블의 이름이다.
  copy_indexes	인덱스들도 복사해 줄 것인지 명시한다. 0: 인덱스를 복사하지 않는다. dbms_redefinition.cons_orig_params: 인덱스를 복사한다.
  copy_triggers	TRUE: 트리거를 복사한다. FALSE: 트리거를 복사하지 않는다.
  copy_constraints	TRUE: 제약 사항들을 복사한다. FALSE: 제약 사항들을 복사하지 않는다.
  copy_privileges	TRUE: 테이블의 권한을 복사한다. FALSE: 테이블의 권한을 복사하지 않는다.
  ignore_errors	TRUE: 관련있는 객체를 복사하며 발생하는 에러는 무시하고 넘어간다. FALSE: 관련있는 객체를 복사하며 에러가 발생하면 멈춘다.
  num_errors	에러가 발생한 횟수를 담아 준다.
  copy_statistics	테이블의 통계 정보를 복사한다.
  copy_mvlog	테이블의 Materialized View LOG들을 복사한다.

  예제

  CREATE TABLE T1 (A NUMBER PRIMARY KEY, B NUMBER);
  CREATE INDEX I1 ON T1(B);
  CREATE TABLE INT_T1 (A NUMBER, B NUMBER);
  
  BEGIN
      DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'T1', 'INT_T1',
       'A A, B B'); 
  END; 
  /
  
  DECLARE num_errors pls_integer;
  BEGIN
      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TIBERO', 'T1', 'INT_T1',
            DBMS_REDEFINITION.CONS_ORIG_PARAMS,
            TRUE, TRUE, TRUE, TRUE, num_errors, FALSE);
  END;
  /

4. FINISH_REDEF_TABLE

온라인 테이블 재정의를 종료하는 프로시저이다. 

이 프로시저에 원본 테이블과 새 테이블의 이름을 지정하면, 재정의 중에 변경된 데이터를 서로 교체한 후 원본 테이블과 새 테이블의 이름을 동기화한다. 이로써 재정의가 종료된다. 단, 시스템이 객체를 바꾸는 등의 작업을 하기 때문에 해당 테이블에서 수행되는 트랜잭션이 없는 경우에만 프로시저가 종료 작업을 수행한다.

FINISH_REDEF_TABLE 프로시저의 세부 내용은 다음과 같다.

• 프로토타입

  PROCEDURE FINISH_REDEF_TABLE
  (
     uname        IN  VARCHAR2,
     orig_table   IN  VARCHAR2,
     int_table    IN  VARCHAR2
  );

• 파라미터

  파라미터	설명
  uname	원본 테이블과 새 테이블이 속한 스키마의 이름이다.
  orig_table	원본 테이블의 이름이다.
  int_table	새 테이블의 이름이다.

• 예제

  BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE');
  END;
  /

5. START_REDEF_TABLE

온라인 테이블 재정의를 시작하는 프로시저이다. 

이 프로시저에 원본 테이블과 새 테이블의 이름 그리고 컬럼 대응 정보를 지정하면, 원본 테이블의 데이터를 새 테이블에 옮기는 위해 프로시저 실행하기 전에 미리 생성해야 하며, 원본 테이블과 같은 스키마에 존재해야 한다. 

단, 파티셔닝, 인덱스, 제약조건, 트리거와 같은 테이블의 구조와 관련된 객체는 원본 테이블과 같지 않아도 된다.

참고

컬럼 대응 정보란 원본 테이블의 어떤 컬럼을 새 테이블의 어떤 컬럼에 저장할지를 지정하는 것을 말한다.

col_mapping 기술하지 않을 경우 NULL로 원본테이블 모두 컬럼명으로 동일한 이름을 가져오게된다.

START_REDEF_TABLE 프로시저의 세부 내용은 다음과 같다.

• 프로토타입

  PROCEDURE START_REDEF_TABLE
  (
     uname          IN  VARCHAR2,
     orig_table     IN  VARCHAR2,
     int_table      IN  VARCHAR2,
     col_mapping    IN  VARCHAR2        DEFAULT NULL,
     options_flag   IN  BINARY_INTEGER  DEFAULT cons_use_pk
  );

• 파라미터

  파라미터	설명
  uname	원본 테이블과 새 테이블이 속한 스키마의 이름이다.
  orig_table	원본 테이블의 이름이다.
  int_table	새 테이블의 이름이다.
  col_mapping	원본 테이블과 새 테이블의 컬럼 대응 정보이다.
  options_flag	원본 테이블에서 새 테이블로 데이터를 옮길 때 어떤 정보를 사용할 것인지를 지정한다. cons_use_pk: 기본 키의 정보를 사용한다. (지정하지 않을 경우 기본값) cons_use_rowid: ROWID를 사용한다.

• 예제

  create table ORIG_TABLE
  (
    PRODUCT_ID    NUMBER primary key,
    PRODUCT_NAME  VARCHAR2(20),
    PRICE         NUMBER,
    SOLD_DATE     DATE
  )
  /
  insert into ORIG_TABLE values (1, 'Tibero', 10000, SYSDATE)
  /
  commit
  /
  create table INT_TABLE
  (
    ID            NUMBER primary key,
    NAME          VARCHAR2(30),
    PRICE         NUMBER(8,2),
    SOLD_DATE     DATE
  )
  partition by range (SOLD_DATE)
  (
    partition P_2008 values less than ('2009-01-01'),
    partition P_2009 values less than (maxvalue)
  )
  /
  BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE',
       'PRODUCT_ID ID, PRODUCT_NAME NAME, PRICE PRICE, SOLD_DATE SOLD_DATE');
  END;
  /

6. SYNC_INTERIM_TABLE

원본 테이블과 새 테이블의 데이터를 동기화하는 프로시저이다. 

FINISH_REDEF_TABLE 프로시저를 실행해도 동기화가 되지만, 온라인 테이블을 재정의하는 중에 해당 테이블에 DML 문장이 많이 수행되는 경우라면 변경된 데이터를 한꺼번에 반영해야 하므로 처리 속도가 늦어질 수 있다. 또한, 재정의를 종료하는 동안에도 해당 테이블에 트랜잭션이 없어야 하기 때문에 DML 문장의 수행이 제한된다.

SYNC_INTERIM_TABLE 프로시저는 이러한 경우와는 다르게 재정의 중에도 동기화를 수행할 수 있으며, 재정의를 종료하는 시간을 줄일 수 있다.

SYNC_INTERIM_TABLE 프로시저의 세부 내용은 다음과 같다.

• 프로토타입

  PROCEDURE SYNC_INTERIM_TABLE
  (
     uname           IN  VARCHAR2,
     orig_table      IN  VARCHAR2,
     int_table       IN  VARCHAR2
  );

• 파라미터

  파라미터	설명
  uname	원본 테이블과 새 테이블이 속한 스키마의 이름이다.
  orig_table	원본 테이블의 이름이다.
  int_table	새 테이블의 이름이다.

• 예제

  BEGIN
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TIBERO', 'ORIG_TABLE', 'INT_TABLE');
  END;
  /

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

파티션 전환 작업

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

dbms_redefinition 패키지는 원래 online reorg용도로 사용되지만 파티션 전환이라는 미션이 추가가 되면서, 

작업한 내용은 파티션 전환에 내용에 치중되어 있습니다.

dbms_redefinition패키지의 내부의 구현은 Mview를 이용한 방식이며 아래와 같이 3단계로 작업하게 됩니다.
1. 초기 데이터 적재 
2. index 생성, 통계정보 생성, dependency 복사
   (중간중간에 변경이력 sync)
3. 원본테이블과 interim 테이블 exchage

. dbms_redefinition.can_redef_table : 사용가능여부 확인                         

. dbms_redefinition.start_redef_table : 컬럼매핑 작업                     

. dbms_redefinition.sync_interim_table : 데이터 동기화 처리                         

. dbms_redefinition.finish_redef_table : 작업 종료 (Lock 잠시발생)                         

-- 원본테이블 정보.
CREATE TABLE SCOTT.TEST(  ID NUMBER,  COL1  VARCHAR2(10),  COL2  VARCHAR2(10) );
ALTER TABLE SCOTT.TEST ADD  CONSTRAINT TEST_PK  PRIMARY KEY (ID);
GRANT DELETE, INSERT, SELECT, UPDATE ON SCOTT.TEST TO HR;
CREATE PUBLIC SYNONYM TEST FOR SCOTT.TEST;


--1. redefinition 가능여부 확인
-- 내부적으로 mview를 사용하므로, sync키로 pk를 사용할 것인지 rowid를 사용할건지 체크한합니다..
-- sync키를 pk로 결정함..(원본테이블에 pk가 없을 경우 rowid로 사용하면 됩니다.)
--
--  cons_use_pk    CONSTANT BINARY_INTEGER := 1;
--  cons_use_rowid CONSTANT BINARY_INTEGER := 2;
exec dbms_redefinition.can_redef_table('SCOTT','TEST',dbms_redefinition.cons_use_pk);

--2. 파티션으로 전환할 테이블 테이블 생성
CREATE TABLE SCOTT.TEST_INTERIM
(  ID NUMBER,  COL1  VARCHAR2(10),  COL2  VARCHAR2(10) )
PARTITION BY RANGE(ID)
(
  PARTITION TEST_S0001 VALUES LESS THAN (1000000),
  PARTITION TEST_S0002 VALUES LESS THAN (2000000),
  PARTITION TEST_S0003 VALUES LESS THAN (3000000),
  PARTITION TEST_S0004 VALUES LESS THAN (4000000)
)ENABLE ROW MOVEMENT;

-- 3. 초기 데이터 적재
-- 취소할경우 : dbms_redefinition.abort_redef_table('SCOTT','TEST','TEST_INTERIM');
exec dbms_redefinition.c('SCOTT','TEST','TEST_INTERIM',null,dbms_redefinition.cons_use_pk);

-- 4. PK 및 일반 인덱스 생성 
-- 원본테이블과 동기화하기 위해서는 PK를 생성해놔야합니다.
CREATE UNIQUE INDEX SCOTT.TEST_INTERIM_PK ON SCOTT.TEST_INTERIM(ID);
ALTER TABLE SCOTT.TEST_INTERIM ADD  CONSTRAINT TEST_INTERIM_PK  PRIMARY KEY (ID);

-- 3.1. finish전까지 수시로 sync작업을함.
exec dbms_redefinition.sync_interim_table('SCOTT', 'TEST', 'TEST_INTERIM');

-- 5. dependency 복사
-- 원본 테이블에 있는 constraint나 trigger나 권한을 파티션 테이블로 복사합니다/
-- 인덱스와 통계정보 복사까지 해당 프로시저를 이용하면, 어디까지 작업중인지
-- tracking하기가 쉽지 않아, 수동으로 생성하는것이 좋습니다.

set serveroutput on
--
-- copy_indexes = no
-- copy_trigger = yes
-- copy_constraints = yes
-- copy_privileges=yes
-- ignore error = yes
-- copy_statistics = no
--
DECLARE
 num_errors PLS_INTEGER;
BEGIN
  dbms_redefinition.copy_table_dependents('SCOTT','TEST','TEST_INTERIM',0,
TRUE, TRUE, TRUE, TRUE,num_errors, FALSE);
  dbms_output.put_line('Error : '||num_errors);
end;
/

--6. 통계정보 생성
-- 일반테이블에서 파티션 테이블로 전환하므로, 파티션 테이블에 맞게 통계정보를 생성해야한다.
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST_INTERIM',
granularity=>'AUTO',estimate_percent=>1);

--7. constraint와 index명 변경
-- 수동으로 생성하였으므로 원본 테이블의 constraint과 인덱스명에 맞게 변경해야한다.  
alter index SCOTT.TEST_PK rename to TEST_PK_bk;
alter index SCOTT.TEST_INTERIM_PK    rename to TEST_PK;
alter table SCOTT.TEST              rename constraint TEST_PK to TEST_PK_bk;
alter table SCOTT.TEST_INTERIM   rename constraint TEST_INTERIM_PK to TEST_PK;

--8. redefinition 완료
-- 원본 테이블과 파티션 테이블이 exchange됩니다.
-- 마지막 변경이력을 적용하고 테이블명이 바꿔치지 되는것이므로,
--  transaction이 길경우에 완료가 되지 않을수 있으므로 lock 모니터링을 해야합니다.
exec dbms_redefinition.finish_redef_table('SCOTT','TEST','TEST_INTERIM');

--9. 관련된 object compile함.
ALTER PUBLIC SYNONYM TEST COMPILE;

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

REORG 작업 - ONLINE 서비스에서 Table 변경하기

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

1. 재정의 임시 Table 생성

-- 최종적으로 변경하고자 구조로 생성한다.

-- 컬럼순서/이름 등등

CREATE TABLE SPPRS.INT_PR_PROD_META

(

PROD_ID          VARCHAR2 (10) NOT NULL,

CHNL_PROD_ID     VARCHAR2 (10),

CHNL_PROD_YN     CHAR (1),

TOP_MENU_ID      VARCHAR2 (10),

MENU_ID          VARCHAR2 (10),

PROD_NM_HASH     VARCHAR2 (4000),

REG_ID           VARCHAR2 (50) NOT NULL,

REG_DT           DATE NOT NULL,

UPD_ID           VARCHAR2 (50),

UPD_DT           DATE,

META_CLSF_CD     VARCHAR2 (10),

GRP_PROD_YN      VARCHAR2 (1)

)

TABLESPACE TBS_SPPRS_DAT

;

2. Redefinition 여부 확인

--패키지를 이용할 수 있는지는 확인함.

BEGIN

    DBMS_REDEFINITION.CAN_REDEF_TABLE(uname => 'SPPRS', tname => 'TB_PR_PROD_META', options_flag => DBMS_REDEFINITION.CONS_USE_PK);

END;

/

3. 빠른 수행을 위한 PARALLEL 설정

alter table SPPRS.INT_PR_PROD_META parallel 4 nologging;

alter session set db_file_multiblock_read_count=128;

alter session enable parallel dml;

alter session force parallel dml   parallel 4;

alter session force parallel query parallel 4;

4. 임시/원본 테이블 매핑작업

-- 임시테이블 형태로 원본테이블을 재정의 수행

-- col_mapping : NULL 인 경우 동일한 이름으로 정의(구조가 다른 경우 설정해줌)

-- orderby_cols : 임시 테이블에 데이터 동기화 시 정렬기준 옵션

BEGIN

    DBMS_REDEFINITION.START_REDEF_TABLE(

        uname         =>  'SPPRS',

        orig_table    =>  'TB_PR_PROD_META',

        int_table     =>  'INT_PR_PROD_META',

--        col_mapping   =>  'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',

        options_flag  =>  DBMS_REDEFINITION.CONS_USE_PK,

        orderby_cols  =>  'CHNL_PROD_ID, PROD_ID'

        );

END;

/

5. 수동으로 종속객체를 생성

-- 자동복제를 사용하지 않고 수동으로 종속객체를 생성할 경우

-- 테이블 정의를 변경하면서 종속객체의 구조도 변경이 필요한 경우 수동으로 정의

-- REGISTER_DEPEPENDENT_OBJECT를 통해서 등록한다. 

create UNIQUE index SPPRS.PK_INT_PR_PROD_META

on SPPRS.INT_PR_PROD_META ( PROD_ID ) 

tablespace TBS_SPPRS_IDX

parallel 4

nologging;

alter table SPPRS.INT_PR_PROD_META add constraint PK_INT_PR_PROD_META primary key (PROD_ID) using index;

alter table SPPRS.INT_PR_PROD_META    noparallel logging;

alter index SPPRS.PK_INT_PR_PROD_META noparallel logging;

-- DBMS_REDEFINITION.REGISTER_DEPEPENDENT_OBJECT(

--    uname             IN  VARCHAR2,

--    orig_table        IN  VARCHAR2,

--    int_table         IN  VARCHAR2,

--    dep_type          IN  PLS_INTEGER,

--    dep_owner         IN  VARCHAR2,

--    dep_orig_name     IN  VARCHAR2,

--    dep_int_name      IN  VARCHAR2);

BEGIN 

    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (

        uname             => 'SPPRS',

orig_table        => 'TB_PR_PROD_META',

int_table         => 'INT_PR_PROD_META',

dep_type          => DBMS_REDEFINITION.CONS_CONSTRAINT,

        dep_owner         => 'SPPRS',

dep_orig_name     => 'PK_PR_PROD_META',

dep_int_name      => 'PK_INT_PR_PROD_META'

        );

END;

/

BEGIN 

    DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (

        uname             => 'SPPRS',

orig_table        => 'TB_PR_PROD_META',

int_table         => 'INT_PR_PROD_META',

dep_type          => DBMS_REDEFINITION.CONS_INDEX,

        dep_owner         => 'SPPRS',

dep_orig_name     => 'PK_PR_PROD_META',

dep_int_name      => 'PK_INT_PR_PROD_META'

        );

END;

/

6. 종속 객체들 임시 테이블에 재정 및 등록시키는 작업

-- DBMS_REDEFINITION.CONS_ORIG_PARAMS : 인덱스 소스를 매개변수로 사용하여 인덱스 복제, 0:모든인덱스를 복제하지 않는다

-- TRUE 값 : 복제 , FALSE : 복제안함.

-- num_errors : 에러 수를 보여준다.

DECLARE

    num_errors PLS_INTEGER;

BEGIN

    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

        uname             =>  'SPPRS',

        orig_table        =>  'TB_PR_PROD_META',

        int_table         =>  'INT_PR_PROD_META',

        copy_indexes      =>  DBMS_REDEFINITION.CONS_ORIG_PARAMS, 

        copy_triggers     =>  TRUE,

        copy_constraints  =>  TRUE,

        copy_privileges   =>  TRUE,

        ignore_errors     =>  TRUE,

        num_errors        =>  num_errors,

        copy_statistics   =>  TRUE, 

        copy_mvlog        =>  FALSE

        ); 

END;

/

-- 에러 확인

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS ;

-- CONSTRAINTS 확인

SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'INT_PR_PROD_META';

-- If not validated

ALTER TABLE SPPRS.INT_PR_PROD_META MODIFY CONSTRAINTS TMP$$_PK_...0 VALIDATE;

7. 작업중인 데이터에 대한 동기화 작업

-- 대량의 데이터인 경우 데이터 동기화를 위한 작업

-- 온라인 상태에서 작업중인 데이터가 있는경우 동기화가 수행되어야 함.

-- DBMS_REDEFINITION.SYNC_INTERIM_TABLE (

--     uname          IN  VARCHAR2,

--     orig_table     IN  VARCHAR2,

--     int_table      IN  VARCHAR2,

--     part_name      IN  VARCHAR2 := NULL);

BEGIN 

    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SPPRS', 'TB_PR_PROD_META', 'INT_PR_PROD_META');

END;

/

8. 동기화 완료 작업 종료

-- 작업이 완료되면 원본 테이블은 임시테이블의 속성과 데이터로 재구성

-- 작업 수행 동안 원본 LOCK 발생함.

-- DBMS_REDEFINITION.FINISH_REDEF_TABLE (

--     uname       IN  VARCHAR2,

--     orig_table  IN  VARCHAR2,

--     int_table   IN  VARCHAR2,

--     part_name   IN  VARCHAR2 := NULL);

BEGIN

    DBMS_REDEFINITION.FINISH_REDEF_TABLE('SPPRS', 'TB_PR_PROD_META', 'INT_PR_PROD_META');

END;

/