파티션 Table 전환 (dbms_redefinition / Mview)

■ dbms_redefinition 패키지를 이용한 파티션 전환

-- DBMS_REDEFINITION.CAN_REDEF_TABLE

-- DBMS_REDEFINITION.START_REDEF_TABLE

-- DBMS_REDEFINITION.SYNC_INTERIM_TABLE

-- DBMS_REDEFINITION.FINISH_REDEF_TABLE

 

-- 대상 table 생성
CREATE TABLE SPSAC.INI_TB_DP_COMMENT_REPLY
(
PRCHS_ID                  VARCHAR2 (20) NOT NULL,
PROD_ID                   VARCHAR2 (10),
PRCHS_AMT                 NUMBER,
PRCHS_PAYMENT_INFO        VARCHAR2 (1000),
REWARD_INFO               VARCHAR2 (1000), EVENT_ID                  VARCHAR2 (100),
EVENT_RESERVE_RESULT_CD   VARCHAR2 (10),
EVENT_RESERVE_RESPONSE    CLOB,
EVENT_PROC_STATUS_CD      VARCHAR2 (10),
EVENT_RESERVE_DT          DATE DEFAULT SYSDATE,
EVENT_JOIN_RESULT_CD      VARCHAR2 (10),
EVENT_JOIN_DT             DATE,
REWARD_WIDR_RESULT_CD     VARCHAR2 (20),
REWARD_WIDR_DT            DATE,
EVENT_META1               VARCHAR2 (4000),
EVENT_META2               VARCHAR2 (4000),
EVENT_META3               VARCHAR2 (4000),
REG_ID                    VARCHAR2 (50),
-- REG_DT                    DATE DEFAULT SYSDATE,
REG_DT                    DATE DEFAULT SYSDATE NOT NULL,
UPD_ID                    VARCHAR2 (50),
UPD_DT                    DATE DEFAULT SYSDATE
) TABLESPACE TBS_SPPRS_TDE_DAT
  LOB (EVENT_RESERVE_RESPONSE) STORE AS SECUREFILE (TABLESPACE TBS_SPPRS_TDE_IDX)
  PARTITION BY RANGE (REG_DT)
( PARTITION P201810 VALUES LESS THAN (to_date('20181101','YYYYMMDD'))
, PARTITION P201811 VALUES LESS THAN (to_date('20181201','YYYYMMDD'))
, PARTITION P201812 VALUES LESS THAN (to_date('20190101','YYYYMMDD'))
, PARTITION P201901 VALUES LESS THAN (to_date('20190201','YYYYMMDD'))
, PARTITION P201902 VALUES LESS THAN (to_date('20190301','YYYYMMDD'))
, PARTITION P201903 VALUES LESS THAN (to_date('20190401','YYYYMMDD'))
, PARTITION P201904 VALUES LESS THAN (to_date('20190501','YYYYMMDD'))
, PARTITION P201905 VALUES LESS THAN (to_date('20190601','YYYYMMDD'))
, PARTITION P201906 VALUES LESS THAN (to_date('20190701','YYYYMMDD'))
, PARTITION P201907 VALUES LESS THAN (to_date('20190801','YYYYMMDD'))
, PARTITION P201908 VALUES LESS THAN (to_date('20190901','YYYYMMDD'))
, PARTITION P201909 VALUES LESS THAN (to_date('20191001','YYYYMMDD'))
, PARTITION P201910 VALUES LESS THAN (to_date('20191101','YYYYMMDD'))
, PARTITION P201911 VALUES LESS THAN (to_date('20191201','YYYYMMDD'))
, PARTITION P201912 VALUES LESS THAN (to_date('20200101','YYYYMMDD'))
, PARTITION PMAX    VALUES LESS THAN (MAXVALUE)
) ENABLE ROW MOVEMENT

 

BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('SPSAC', 'TB_DP_COMMENT_REPLY', options_flag  =>  DBMS_REDEFINITION.CONS_USE_PK);
END;
/



BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE(
        uname         =>  'SPSAC',
        orig_table    =>  'TB_DP_COMMENT_REPLY',
        int_table     =>  'INI_TB_DP_COMMENT_REPLY',
        col_mapping   =>  'REPLY_SEQ       REPLY_SEQ     ,
       to_clob(REPLY_DSCR)      REPLY_DSCR    ,
                   COMMENT_SEQ     COMMENT_SEQ   ,
                   MBR_NO          MBR_NO        ,
                   NICKNAME        NICKNAME      ,
                   USER_MDN        USER_MDN      ,
                   AUTHOR_YN       AUTHOR_YN     ,
                   PURCHASE_YN     PURCHASE_YN   ,
                   REPLY_ORD       REPLY_ORD     ,
                   RECOM_CNT       RECOM_CNT     ,
                   BAD_REPLY_YN    BAD_REPLY_YN  ,
                   REG_ID          REG_ID        ,
                   REG_DT          REG_DT        ,
                   UPD_ID          UPD_ID        ,
                   UPD_DT          UPD_DT        ,
                   DEL_YN          DEL_YN        ,
                   DEL_TYPE        DEL_TYPE      ,
                   DEL_DT          DEL_DT      ' ,
        options_flag  =>  DBMS_REDEFINITION.CONS_USE_PK--,
--        orderby_cols  =>  'REPLY_SEQ'
        );
END;
/



DECLARE
    num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
        uname             =>  'SPSAC',
        orig_table        =>  'TB_DP_COMMENT_REPLY',
        int_table         =>  'INI_TB_DP_COMMENT_REPLY',
        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
;


SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'INI_TB_DP_COMMENT_REPLY';
-- 확인하기
ALTER TABLE SPSAC.INI_TB_DP_COMMENT_REPLY MODIFY CONSTRAINTS TMP$$_PK_CM_VOC_EMAIL0 VALIDATE;

-- 변경 데이터 sync 작업
BEGIN 
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SPSAC', 'TB_DP_COMMENT_REPLY', 'INI_TB_DP_COMMENT_REPLY');
END;
/


BEGIN 
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SPSAC', 'TB_DP_COMMENT_REPLY', 'INI_TB_DP_COMMENT_REPLY');
END;
/

-- 작업 완료
BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('SPSAC', 'TB_DP_COMMENT_REPLY', 'INI_TB_DP_COMMENT_REPLY');
END;
/

drop table SPSAC.INI_TB_DP_COMMENT_REPLY purge;

 

 

 

■ Mview를 이용한 파티션 전환

 

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. 원본 테이블에 primary key모드로  Mlog생성합니다. 
CREATE MATERIALIZED VIEW LOG ON SCOTT.TEST  WITH PRIMARY KEY EXCLUDING NEW VALUES;

-- 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. 원본 테이블에 primary key모드로 Mview 생성합니다. 
-- 수동으로 refresh할 예정이므로,  START WITH ..  NEXT 절은 생략합니다.
-- START WITH ..  NEXT 절을 사용하면 job에 등록되어지기때문에 
-- 자동으로 refresh되어 예기치 않는 문제가 발생될수 있습니다.
CREATE MATERIALIZED VIEW SCOTT.TEST_INTERIM
ON PREBUILT TABLE 
REFRESH FORCE
WITH PRIMARY KEY
AS
SELECT * FROM SCOTT.TEST;

-- 4. TEST_INTERIM_PK 인덱스 생성합니다.
CREATE INDEX SCOTT.TEST_INTERIM_PK ON SCOTT.TEST_INTERIM (ID);

-- 5. Primary Key Constraint 생성합니다.
ALTER TABLE SCOTT.TEST_INTERIM ADD  CONSTRAINT TEST_INTERIM_PK  PRIMARY KEY (ID);

-- 6. 원본테이블와 동기화작업합니다.
EXEC DBMS_MVIEW.REFRESH('"SCOTT"."TEST_INTERIM"');

-- 7. 원본테이블과 동일하게 권한을 부여합니다. 
GRANT DELETE, INSERT, SELECT, UPDATE ON SCOTT.TEST_INTERIM TO HR;

-- 9. 통계정보 수집합니다.(혹시 plan이 변경될수 있으므로 원본테이블의 통계쩡보를  copy하셔도 됩니다).
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST_INTERIM', granularity=>'AUTO',estimate_percent=>1); 

-- 10. 미리 인덱스/constraint명을 수정함
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; 

-- 12. 원본테이블와 동기화작업합니다.
EXEC DBMS_MVIEW.REFRESH('"SCOTT"."TEST_INTERIM"');

-- 13 ~ 15단계는 offline작업이므로, 빠르게 진행합니다.
-- 13. 물리적인 테이블을 남겨두고, Mview 삭제함.
DROP MATERIALIZED VIEW SCOTT.TEST_INTERIM PRESERVE TABLE;

-- 14. 원본테이블에 Mlog를 삭제함.
-- 원본테이블명을 rename하기 위해서는 mlog가 삭제되어야합니다.
DROP MATERIALIZED VIEW LOG ON SCOTT.TEST;

--15. 테이블명을 수정함,
ALTER TABLE SCOTT.TEST RENAME TO TEST_BK;
ALTER TABLE SCOTT.TEST_INTERIM RENAME TO TEST;

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