Oracle生産非分區表改成分區表（業務低峰期）

--生産非分區熱表改成分區表

-- CREATE TABLE 建立臨時分區表

CREATE TABLE RS.T_RC_CD_MC_SM_0926

(

ENY_CD_NO VARCHAR2(200) NOT NULL,

SB_CD_NO VARCHAR2(20) NOT NULL,

IN_NO VARCHAR2(20) NOT NULL,

TN_DT VARCHAR2(8) NOT NULL,

DY_SM NUMBER(13,2) NOT NULL

)

PARTITION BY RANGE (TN_DT)

(

PARTITION P201801 VALUES LESS THAN ('20180101')

TABLESPACE RS_DATA,

PARTITION P201802 VALUES LESS THAN ('20180201')

TABLESPACE RS_DATA,

PARTITION P201803 VALUES LESS THAN ('20180301')

TABLESPACE RS_DATA,

PARTITION P201804 VALUES LESS THAN ('20180401')

TABLESPACE RS_DATA,

PARTITION P201805 VALUES LESS THAN ('20180501')

TABLESPACE RS_DATA,

PARTITION P201806 VALUES LESS THAN ('20180601')

TABLESPACE RS_DATA,

PARTITION P201807 VALUES LESS THAN ('20180701')

TABLESPACE RS_DATA,

PARTITION P201808 VALUES LESS THAN ('20180801')

TABLESPACE RS_DATA,

PARTITION P201809 VALUES LESS THAN ('20180901')

TABLESPACE RS_DATA,

PARTITION P201810 VALUES LESS THAN ('20181001')

TABLESPACE RS_DATA,

PARTITION P201811 VALUES LESS THAN ('20181101')

TABLESPACE RS_DATA,

PARTITION P201812 VALUES LESS THAN ('20181201')

TABLESPACE RS_DATA,

PARTITION P201901 VALUES LESS THAN ('20190101')

TABLESPACE RS_DATA,

PARTITION P201902 VALUES LESS THAN ('20190201')

TABLESPACE RS_DATA,

PARTITION P201903 VALUES LESS THAN ('20190301')

TABLESPACE RS_DATA,

PARTITION P201904 VALUES LESS THAN ('20190401')

TABLESPACE RS_DATA,

PARTITION P201905 VALUES LESS THAN ('20190501')

TABLESPACE RS_DATA,

PARTITION P201906 VALUES LESS THAN ('20190601')

TABLESPACE RS_DATA,

PARTITION P201907 VALUES LESS THAN ('20190701')

TABLESPACE RS_DATA,

PARTITION P201908 VALUES LESS THAN ('20190801')

TABLESPACE RS_DATA,

PARTITION P201909 VALUES LESS THAN ('20190901')

TABLESPACE RS_DATA,

PARTITION P201910 VALUES LESS THAN ('20191001')

TABLESPACE RS_DATA,

PARTITION P201911 VALUES LESS THAN ('20191101')

TABLESPACE RS_DATA,

PARTITION P201912 VALUES LESS THAN ('20191201')

TABLESPACE RS_DATA,

PARTITION P202001 VALUES LESS THAN ('20200101')

TABLESPACE RS_DATA,

PARTITION P202002 VALUES LESS THAN ('20200201')

TABLESPACE RS_DATA,

PARTITION PMAX VALUES LESS THAN (MAXVALUE)

TABLESPACE RS_DATA

);

-- ADD COMMENTS TO THE TABLE

COMMENT ON TABLE RS.T_RC_CD_MC_SM_0926

IS '商戶+卡交易金額累加表';

-- ADD COMMENTS TO THE COLUMNS

COMMENT ON COLUMN RS.T_RC_CD_MC_SM_0926.ENY_CD_NO

IS '卡号密文';

COMMENT ON COLUMN RS.T_RC_CD_MC_SM_0926.SB_CD_NO

IS '卡号';

COMMENT ON COLUMN RS.T_RC_CD_MC_SM_0926.IN_NO

IS '商編';

COMMENT ON COLUMN RS.T_RC_CD_MC_SM_0926.TN_DT

IS '交易日期';

COMMENT ON COLUMN RS.T_RC_CD_MC_SM_0926.DY_SM

IS '累計金額';

-- CREATE/RECREATE PRIMARY, UNIQUE AND FOREIGN KEY CONSTRAINTS

ALTER TABLE RS.T_RC_CD_MC_SM_0926 ADD CONSTRAINT PK_T_RC_CD_MC_SM_0926 PRIMARY KEY (ENY_CD_NO, IN_NO, TN_DT) USING INDEX TABLESPACE RS_IDX LOCAL;

--導出需要的原表資料

expdp \'/ as sysdba\' dumpfile=t_rc_cd_mc_sm_180901.dmp directory=exp_shengchan logfile=t_rc_cd_mc_sm_180901.log \

tables=rs.t_rc_cd_mc_sm query=rs.t_rc_cd_mc_sm:\"where tran_dt\>\=\'20180901\'\"

---30秒

--将資料導入到臨時表

impdp \'/ as sysdba\' directory=exp_shengchan dumpfile=t_rc_cd_mc_sm_180901.dmp logfile=t_rc_cd_mc_sm_180901.log \

remap_table=rs.t_rc_cd_mc_sm:t_rc_cd_mc_sm_0926

-----CREATE TABLE RS.T_RCS_CRD_MEC_SUM_09261 TABLESPACE RS_DATA AS SELECT * FROM RS.T_RC_CD_MC_SM WHERE TN_DT >='20180901'

1.将原表RENAME 成BAK表----原表RENAME BAK RS.T_RC_CD_MC_SM RS.T_RC_CD_MC_SM_BAK

---RENAME INDEX

ALTER INDEX RS.PK_T_RC_CD_MC_SM RENAME TO PK_T_RC_CD_MC_SM_BAK;

--RENAME PK

ALTER TABLE RS.T_RC_CD_MC_SM RENAME CONSTRAINT PK_T_RC_CD_MC_SM TO PK_T_RC_CD_MC_SM_BAK;

--RENAME TABLE

ALTER TABLE RS.T_RC_CD_MC_SM RENAME TO T_RC_CD_MC_SM_BAK;

2.臨時表 RENAME 成原表

----臨時表 RENAME 成原表 RS.T_RC_CD_MC_SM_0926 RS.T_RC_CD_MC_SM

ALTER INDEX PTS.PK_T_RC_CD_MC_SM_0926 RENAME TO PK_T_RC_CD_MC_SM;

--RENAME PK

ALTER TABLE RS.T_RC_CD_MC_SM_0926 RENAME CONSTRAINT PK_T_RC_CD_MC_SM_0926 TO PK_T_RC_CD_MC_SM;

--RENAME TABLE

ALTER TABLE RS.T_RC_CD_MC_SM_0926 RENAME TO T_RC_CD_MC_SM;

3.收集新表統計資訊

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS(OWNNAME => 'RS',TABNAME => 'T_RC_CD_MC_SM');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'RS',TABNAME => 'T_RC_CD_MC_SM',ESTIMATE_PERCENT => 30,METHOD_OPT=> 'FOR ALL INDEXED COLUMNS',DEGREE=>16,CASCADE=>TRUE);