海量資料處理_資料泵分批資料遷移

根據系統規劃，需要將包含2億條記錄的表從A系統遷移到B系統（均為AIX+ORACLE10.2）,主要問題是需要在半個小時的投產視窗內完成遷移。
根據此要求，採用分批expdp/impdp方式實現。假設投產日為D日：
  * 第一批，在D-1日8點,遷移D-1日之前（  * 第二批，在D日0點,遷移D-1日(>=D-1且

  如果投產日D日為20110709,則各批次資料為:
  * 第一批，在20110708日8點,遷移20110708日之前的餘額資料;
  * 第二批，在20110710日0點,遷移20110708當日的餘額資料;

操作說明：
1,匯出
1.1第一批匯出
expdp user/pwd parfile=expdp_HIS_BAL.par
注:請根據投產時間修改匯出資料範圍。

1.2,第二批匯出
expdp user/pwd parfile=expdp_HIS_BAL2.par
注:請根據投產時間修改匯出資料範圍。

2,匯入
2.1建立表
sqlplus user/pwd @create_table_ghb.sql

2.2第一批匯入
impdp user/pwd parfile=impdp_HIS_BAL.par
注:請根據匯出檔案修改匯入檔案列表。

2.3第二批匯入
impdp user/pwd parfile=impdp_HIS_BAL2.par
注:請根據匯出檔案修改匯入檔案列表。

2.4刪除列,建立索引,收集統計資訊(根據需要執行)
sqlplus user/pwd @create_index_ghb.sql

----------------------------------------------------------
附件1,expdp_HIS_BAL.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL%U.dmp
logfile=exp_HIS_BAL.log
tables=HIS_BAL
QUERY=HIS_BAL:"WHERE upddat< to_date('20110708','YYYYMMDD')"
exclude=index

附件2,expdp_HIS_BAL2.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL2%U.dmp
logfile=exp_HIS_BAL2.log
tables=HIS_BAL
QUERY=HIS_BAL:"WHERE upddat >= to_date('20110708','YYYYMMDD') and upddat< to_date('20110709','YYYYMMDD')"
exclude=index

附件3,impdp_HIS_BAL.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL%U.dmp
logfile=imp_HIS_BAL.log
TABLE_EXISTS_ACTION=APPEND
remap_schema=bocnet:mh

附件4,impdp_HIS_BAL2.par:
PARALLEL=8
DIRECTORY=EXPDP_DIR
DUMPFILE=HIS_BAL2%U.dmp
logfile=imp_HIS_BAL2.log
TABLE_EXISTS_ACTION=APPEND
remap_schema=bocnet:mh

附件5,create_table_ghb.sql:
CREATE TABLE HIS_BAL
( UPDDAT DATE not null,
  ...
)
NOLOGGING
PARTITION BY RANGE(UPDDAT)
( partition p200912 values less than (to_date('20100101','YYYYMMDD')),
  partition p201001 values less than (to_date('20100201','YYYYMMDD')),
  partition p201002 values less than (to_date('20100301','YYYYMMDD')),
  partition p201003 values less than (to_date('20100401','YYYYMMDD')),
  partition p201004 values less than (to_date('20100501','YYYYMMDD')),
  partition p201005 values less than (to_date('20100601','YYYYMMDD')),
  partition p201006 values less than (to_date('20100701','YYYYMMDD')),
  partition p201007 values less than (to_date('20100801','YYYYMMDD')),
  partition p201008 values less than (to_date('20100901','YYYYMMDD')),
  partition p201009 values less than (to_date('20101001','YYYYMMDD')),
  partition p201010 values less than (to_date('20101101','YYYYMMDD')),
  partition p201011 values less than (to_date('20101201','YYYYMMDD')),
  partition p201012 values less than (to_date('20110101','YYYYMMDD')),
  partition p201101 values less than (to_date('20110201','YYYYMMDD')),
  partition p201102 values less than (to_date('20110301','YYYYMMDD')),
  partition p201103 values less than (to_date('20110401','YYYYMMDD')),
  partition p201104 values less than (to_date('20110501','YYYYMMDD')),
  partition p201105 values less than (to_date('20110601','YYYYMMDD')),
  partition p201106 values less than (to_date('20110701','YYYYMMDD')),
  partition p201107 values less than (to_date('20110801','YYYYMMDD')),
  partition p201108 values less than (to_date('20110901','YYYYMMDD')),
  partition p201109 values less than (to_date('20111001','YYYYMMDD')),
  partition p201110 values less than (to_date('20111101','YYYYMMDD')),
  partition p201111 values less than (to_date('20111201','YYYYMMDD')),
  partition p201112 values less than (to_date('20120101','YYYYMMDD')),
  partition p201201 values less than (to_date('20120201','YYYYMMDD')),
  partition p201202 values less than (to_date('20120301','YYYYMMDD')),
  partition p201203 values less than (to_date('20120401','YYYYMMDD')),
  partition p201204 values less than (to_date('20120501','YYYYMMDD')),
  partition p201205 values less than (to_date('20120601','YYYYMMDD')),
  partition p201206 values less than (to_date('20120701','YYYYMMDD')),
  partition p201207 values less than (to_date('20120801','YYYYMMDD')),
  partition p201208 values less than (to_date('20120901','YYYYMMDD')),
  partition p201209 values less than (to_date('20121001','YYYYMMDD')),
  partition p201210 values less than (to_date('20121101','YYYYMMDD')),
  partition p201211 values less than (to_date('20121201','YYYYMMDD')),
  partition p201212 values less than (to_date('20130101','YYYYMMDD')),
  partition pmax values less than (maxvalue)
)
;

附件6,create_index_ghb.sql:
prompt 1,更改日誌屬性
alter table HIS_BAL logging;

prompt 2,analyze table:
begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'HIS_BAL',degree => 8);
end;
/

prompt 3,create index

CREATE UNIQUE INDEX PK_HIS_BAL ON HIS_BAL(...)
  LOCAL
  PARALLEL 8
  NOLOGGING;

ALTER INDEX PK_HIS_BAL logging noparallel;

ALTER TABLE HIS_BAL ADD CONSTRAINT PK_HIS_BAL PRIMARY KEY(...)
USING INDEX
LOCAL;

create index IDX_HIS_BAL_UPDDAT on HIS_BAL(UPDDAT)
local
parallel 8
nologging;

ALTER INDEX IDX_HIS_BAL_UPDDAT logging noparallel;