파티션 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;