Redefinition

아래의 글은  Redefinition 전체 과정을 모두 적어 놓은 것이다. 

모델링 재정의가 필요한 테이블을 DBMS_REDEFINITION.CAN_REDEF_TABLE 다음 부분과, VIEW이름을 수정하면 그래로 수행 된다

 

  CREATE OR REPLACE OUTLINE ILM_OUTLINE FOR CATEGORY O$VAN_QUE_MNT_1MIN ON SELECT 1 FROM "TOBIT"."VAN_QUE_MNT_1MIN";
 
  select OL_NAME,CATEGORY,HINT#,HINT_TEXT from OUTLN.OL$HINTS where HINT# = 1 AND CATEGORY='O$VAN_QUE_MNT_1MIN';
 
  update OUTLN.OL$HINTS set HINT_TEXT = 'NO_PARALLEL FULL(@"SEL$1")'
  where HINT# = 1 and CATEGORY = 'O$VAN_QUE_MNT_1MIN';
  commit;
 
  select OL_NAME,CATEGORY,HINT#,HINT_TEXT from OUTLN.OL$HINTS where HINT# = 1 AND CATEGORY='O$VAN_QUE_MNT_1MIN';
 
  alter session set USE_STORED_OUTLINES=O$VAN_QUE_MNT_1MIN;
 
  alter session set optimizer_mode=all_rows;
 
  alter session set events '10359 trace name context forever, level 1';
  alter session set events '10995 trace name context forever, level 2';
 
  alter session set "_partition_large_extents"=FALSE;
  alter session set "_index_partition_large_extents"=FALSE;
 
 
  -- alter session set "ddl_lock_timeout"=60;
 
 
--@@ 
/*
# CAN_REDEF_TABLE
온라인 테이블 재정의가 가능한지 확인하는 프로시저. 가능하지 않을 경우 에러 발생됨.  -- 유니크하지 않을 경우

프로토타입
PROCEDURE CAN_REDEF_TABLE 
(
    uname IN VARCHAR2, 
    tname IN VARCHAR12,
    options_flag IN BINARY_INTEGER := 1, 
    part_name IN VARCHAR2 := NULL
);

파라미터


파라미터 설명
uname           테이블이 속한 스키마의 이름이다. 
tname           온라인 테이블 재정의가 가능한지 확인할 테이블의 이름이다. 
options_flag    원본 테이블에서 새 테이블로 데이터를 옮길 때 어떤 정보를 사용할 것인지를 지정한다.
cons_use_pk     기본 키의 정보를 사용한다. (지정하지 않을 경우 기본값)
cons_use_rowid  ROWID를 사용한다.
part_name       재정의할 파티션의 이름이다. 한 테이블의 단 하나의 파티션을 재정의하는 경우에는 파티션의 이름을 해당 파라미터로 명시해야 한다. 
NULL은 전체 테이블이 재정의된다는 것을 의미한다.
 

*/
 
  EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TOBIT', 'VAN_QUE_MNT_1MIN',DBMS_REDEFINITION.CONS_USE_PK);
 
  CREATE TABLE TOBIT.VAN_QUE_MNT_1MIN_$T
  (
     STIME               VARCHAR2(14 BYTE)                   DEFAULT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') NOT NULL
    ,TSDATE              VARCHAR2(14 BYTE)                   NOT NULL
    ,HOSTNAME            VARCHAR2(10 BYTE)                   NOT NULL
    ,Q_KEY               NUMBER(10,0)                        NOT NULL
    ,SRC_GNAME           VARCHAR2(6 BYTE)                    
    ,SRC_SNAME           VARCHAR2(8 BYTE)                    
    ,SRC_SEQNO           NUMBER(2,0)                         NOT NULL
    ,Q_CUR_DATA          NUMBER(10,0)                        
    ,Q_MAX_DATA          NUMBER(10,0)                        
    ,GROUP_TYPE          VARCHAR2(4 BYTE)                    
  )
  SEGMENT CREATION IMMEDIATE
  PCTFREE 10  INITRANS 1  MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_DATA
  PARTITION BY RANGE(STIME)
  (
    PARTITION P201411           VALUES LESS THAN ('20141201000000') TABLESPACE SVM_DATA,
    PARTITION P201412           VALUES LESS THAN ('20150101000000') TABLESPACE SVM_DATA,
    PARTITION P201501           VALUES LESS THAN ('20150201000000') TABLESPACE SVM_DATA,
    PARTITION P201502           VALUES LESS THAN ('20150301000000') TABLESPACE SVM_DATA,
    PARTITION P201503           VALUES LESS THAN ('20150401000000') TABLESPACE SVM_DATA,
    PARTITION P201504           VALUES LESS THAN ('20150501000000') TABLESPACE SVM_DATA,
    PARTITION P201505           VALUES LESS THAN ('20150601000000') TABLESPACE SVM_DATA,
    PARTITION P201506           VALUES LESS THAN ('20150701000000') TABLESPACE SVM_DATA,
    PARTITION P201507           VALUES LESS THAN ('20150801000000') TABLESPACE SVM_DATA,
    PARTITION P201508           VALUES LESS THAN ('20150901000000') TABLESPACE SVM_DATA,
    PARTITION P201509           VALUES LESS THAN ('20151001000000') TABLESPACE SVM_DATA,
    PARTITION P201510           VALUES LESS THAN ('20151101000000') TABLESPACE SVM_DATA,
    PARTITION P201511           VALUES LESS THAN ('20151201000000') TABLESPACE SVM_DATA,
    PARTITION P201512           VALUES LESS THAN ('20160101000000') TABLESPACE SVM_DATA,
    PARTITION P201601           VALUES LESS THAN ('20160201000000') TABLESPACE SVM_DATA,
    PARTITION P201602           VALUES LESS THAN ('20160301000000') TABLESPACE SVM_DATA,
    PARTITION P201603           VALUES LESS THAN ('20160401000000') TABLESPACE SVM_DATA,
    PARTITION P201604           VALUES LESS THAN ('20160501000000') TABLESPACE SVM_DATA,
    PARTITION P201605           VALUES LESS THAN ('20160601000000') TABLESPACE SVM_DATA,
    PARTITION P201606           VALUES LESS THAN ('20160701000000') TABLESPACE SVM_DATA,
    PARTITION P201607           VALUES LESS THAN ('20160801000000') TABLESPACE SVM_DATA,
    PARTITION P201608           VALUES LESS THAN ('20160901000000') TABLESPACE SVM_DATA,
    PARTITION P201609           VALUES LESS THAN ('20161001000000') TABLESPACE SVM_DATA,
    PARTITION P201610           VALUES LESS THAN ('20161101000000') TABLESPACE SVM_DATA,
    PARTITION P201611           VALUES LESS THAN ('20161201000000') TABLESPACE SVM_DATA,
    PARTITION P201612           VALUES LESS THAN ('20170101000000') TABLESPACE SVM_DATA,
    PARTITION P201701           VALUES LESS THAN ('20170201000000') TABLESPACE SVM_DATA,
    PARTITION P201702           VALUES LESS THAN ('20170301000000') TABLESPACE SVM_DATA,
    PARTITION P201703           VALUES LESS THAN ('20170401000000') TABLESPACE SVM_DATA,
    PARTITION P201704           VALUES LESS THAN ('20170501000000') TABLESPACE SVM_DATA,
    PARTITION P201705           VALUES LESS THAN ('20170601000000') TABLESPACE SVM_DATA,
    PARTITION P201706           VALUES LESS THAN ('20170701000000') TABLESPACE SVM_DATA,
    PARTITION P201707           VALUES LESS THAN ('20170801000000') TABLESPACE SVM_DATA,
    PARTITION P201708           VALUES LESS THAN ('20170901000000') TABLESPACE SVM_DATA,
    PARTITION P201709           VALUES LESS THAN ('20171001000000') TABLESPACE SVM_DATA,
    PARTITION P201710           VALUES LESS THAN ('20171101000000') TABLESPACE SVM_DATA,
    PARTITION P201711           VALUES LESS THAN ('20171201000000') TABLESPACE SVM_DATA,
    PARTITION P201712           VALUES LESS THAN ('20180101000000') TABLESPACE SVM_DATA,
    PARTITION P201801           VALUES LESS THAN ('20180201000000') TABLESPACE SVM_DATA,
    PARTITION P201802           VALUES LESS THAN ('20180301000000') TABLESPACE SVM_DATA,
    PARTITION P201803           VALUES LESS THAN ('20180401000000') TABLESPACE SVM_DATA,
    PARTITION P201804           VALUES LESS THAN ('20180501000000') TABLESPACE SVM_DATA,
    PARTITION P201805           VALUES LESS THAN ('20180601000000') TABLESPACE SVM_DATA,
    PARTITION P201806           VALUES LESS THAN ('20180701000000') TABLESPACE SVM_DATA,
    PARTITION P201807           VALUES LESS THAN ('20180801000000') TABLESPACE SVM_DATA,
    PARTITION P201808           VALUES LESS THAN ('20180901000000') TABLESPACE SVM_DATA,
    PARTITION P201809           VALUES LESS THAN ('20181001000000') TABLESPACE SVM_DATA,
    PARTITION P201810           VALUES LESS THAN ('20181101000000') TABLESPACE SVM_DATA
  )  ;
 

  COMMENT ON  TABLE TOBIT.VAN_QUE_MNT_1MIN_$T IS 'VAN 프로세스 큐 현황 수집 테이블';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.STIME IS '수집일시';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.TSDATE IS '마지막 업데이트 시간';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.HOSTNAME IS '호스트 명칭';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.Q_KEY IS '큐 아이디';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.SRC_GNAME IS '그룹이름';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.SRC_SNAME IS '프로세스명';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.SRC_SEQNO IS '시퀀스';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.Q_CUR_DATA IS '현재 큐 적재량 (단위 BYTE)';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.Q_MAX_DATA IS '전체 큐 적재량 (단위 BYTE)';
  COMMENT ON COLUMN TOBIT.VAN_QUE_MNT_1MIN_$T.GROUP_TYPE IS '그룹 타입';
 
  ALTER SESSION FORCE PARALLEL DML PARALLEL 4; 
  ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4; 
 
 --@@
 
 -- @@
 
/*
# 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를 사용한다.


*/
 
 

  EXEC DBMS_REDEFINITION.START_REDEF_TABLE('TOBIT', 'VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T',options_flag=>DBMS_REDEFINITION.CONS_USE_PK,orderby_cols=>NULL);
  

  CREATE INDEX TOBIT.IDX_VQM_001_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( STIME  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL PREFIXED Partition Index */
  ;
  CREATE INDEX TOBIT.IDX_VQM_002_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( HOSTNAME  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL NON_PREFIXED Partition Index */
  ;
  CREATE INDEX TOBIT.IDX_VQM_003_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( Q_KEY  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL NON_PREFIXED Partition Index */
  ;
  CREATE INDEX TOBIT.IDX_VQM_004_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( GROUP_TYPE  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL NON_PREFIXED Partition Index */
  ;
  CREATE INDEX TOBIT.IDX_VQM_005_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( SRC_SNAME  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL NON_PREFIXED Partition Index */
  ;
  CREATE UNIQUE INDEX TOBIT.VAN_QUE_MNT_1MIN_PK_$T ON TOBIT.VAN_QUE_MNT_1MIN_$T ( STIME , HOSTNAME , Q_KEY , SRC_SEQNO  )
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  NOLOGGING PARALLEL 4
  TABLESPACE SVM_IDX
  LOCAL        /* LOCAL PREFIXED Partition Index */
  ;

  ALTER TABLE TOBIT.VAN_QUE_MNT_1MIN_$T ADD CONSTRAINT VAN_QUE_MNT_1MIN_PK_$T PRIMARY KEY (STIME , HOSTNAME , Q_KEY , SRC_SEQNO ) USING INDEX TOBIT.VAN_QUE_MNT_1MIN_PK_$T;

 
  ALTER INDEX TOBIT.IDX_VQM_001_$T LOGGING NOPARALLEL;
  ALTER INDEX TOBIT.IDX_VQM_002_$T LOGGING NOPARALLEL;
  ALTER INDEX TOBIT.IDX_VQM_003_$T LOGGING NOPARALLEL;
  ALTER INDEX TOBIT.IDX_VQM_004_$T LOGGING NOPARALLEL;
  ALTER INDEX TOBIT.IDX_VQM_005_$T LOGGING NOPARALLEL;
  ALTER INDEX TOBIT.VAN_QUE_MNT_1MIN_PK_$T LOGGING NOPARALLEL;
 
  ALTER TABLE TOBIT.VAN_QUE_MNT_1MIN_$T LOGGING NOPARALLEL;
 
 
 /*
 # REGISTER_DEPENDENT_OBJECT
 기존 테이블의 관련 객체와 새 테이블의 관련 객체를 매핑시킨다. 온라인 테이블 재정의가 끝나면 매핑시킨 객체들의 이름이 서로 바뀌게 된다.
 
프로토타입
PROCEDURE REGISTER_DEPENDENT_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 
);



파라미터 설명
uname 원본 테이블과 새 테이블이 속한 스키마의 이름이다. 
orig_table 원본 테이블의 이름이다. 
int_table 새 테이블의 이름이다. 
dep_type 관련 객체의 타입이다. 
dep_owner 관련 객체를 소유한 사용자 이름이다. 
dep_orig_name 원본 테이블의 관련 객체 이름이다. 
dep_int_name 새 테이블의 관련 객체 이름이다. 
 
 */
 
 
 
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','IDX_VQM_001','IDX_VQM_001_$T');
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','IDX_VQM_002','IDX_VQM_002_$T');
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','IDX_VQM_003','IDX_VQM_003_$T');
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','IDX_VQM_004','IDX_VQM_004_$T');
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','IDX_VQM_005','IDX_VQM_005_$T');
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_INDEX, 'TOBIT','VAN_QUE_MNT_1MIN_PK','VAN_QUE_MNT_1MIN_PK_$T');
 
  EXEC DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T', DBMS_REDEFINITION.CONS_CONSTRAINT, 'TOBIT','VAN_QUE_MNT_1MIN_PK','VAN_QUE_MNT_1MIN_PK_$T');
 
 
 
  ALTER SESSION DISABLE PARALLEL DML; 
  ALTER SESSION DISABLE PARALLEL QUERY; 
 
-- Optional (Sync)

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


프로토타입
PROCEDURE SYNC_INTERIM_TABLE 
( 
    uname IN VARCHAR2, 
    orig_table IN VARCHAR2,
    int_table IN VARCHAR2 
);



파라미터 설명

uname 원본 테이블과 새 테이블이 속한 스키마의 이름이다. 
orig_table 원본 테이블의 이름이다. 
int_table 새 테이블의 이름이다. 

*/
  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
 
  EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TOBIT',tabname=>'VAN_QUE_MNT_1MIN_$T', estimate_percent=>.3, cascade=>TRUE, degree=>4,method_opt=>'FOR ALL COLUMNS SIZE AUTO',granularity=>'ALL');
 
-- IF REDEFINITION Error Occurred 
-- EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
 
  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
  EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
  EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TOBIT','VAN_QUE_MNT_1MIN','VAN_QUE_MNT_1MIN_$T');
 
 
 
-- DROP TABLE TOBIT.VAN_QUE_MNT_1MIN_$T PURGE;
 

 





@@ 

ORA-23539 에러 의 경우 
 => 재정의중 취소나, KILL 시켰을 경우 아래의 명령어를 통해 재정의를 중지 


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



파라미터설명

uname 원본 테이블과 새 테이블이 속한 스키마의 이름이다. 
orig_table 원본 테이블의 이름이다. 
int_table 새 테이블의 이름이다. 






ABORT_REDEF_TABLE

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



BEGIN
    DBMS_REDEFINITION.ABORT_REDEF_TABLE('TOBIT', 'VAN_QUE_MNT_1MIN', 'VAN_QUE_MNT_1MIN_$T');
END;

 

제25장 DBMS_REDEFINITION