兩個歷史表的資料合併

目的：清理4.0資料庫的表空間P_2014，將該表空間中的歷史表pay.ORDERINFO_HIS的2014年分割槽清理，放入3.0資料庫中的pay.ORDERINFO_HIS表（包含2013年資料）中。
需要考慮的問題：
1）4.0資料庫中的歷史表pay.ORDERINFO_HIS中存在2013年資料，因此直接插入3.0資料庫中的pay.ORDERINFO_HIS表中會報主鍵衝突
2）分割槽表的每個分割槽都超過10G，需要考慮效能
3）4.0資料庫採用ASM儲存，3.0資料庫為檔案系統儲存




可選方案：
1.將3.0資料庫中pay.ORDERINFO_HIS的索引刪除（保留建立語句），impdp匯入，然後建立索引。
在建立主鍵索引和唯一鍵索引前，需要將對應列進行分割槽查詢，剔除重複資料。


2.使用expdp匯出資料，在3.0資料庫中按照分割槽分別建立表pay.ORDERINFO_HIS_201401、……………………、pay.ORDERINFO_HIS_201412，
將資料匯入，然後採用分割槽交換的方式匯入。出現主鍵衝突時可以先消除舊資料。（因為存在更新操作，時間較新的是所需資料）


3.使用可傳輸表空間


4.使用goldengate匯出和匯入


5.在4.0資料庫所在伺服器上使用goldengate的初始化載入將資料匯出成文字格式，傳輸到3.0資料庫所在伺服器，使用sql loader匯入




採用方案1時，建立主鍵索引消耗資源很大，監控出現報警，因此改用方案2了。
採用方案2時，exchange partition操作的時間比較長，但消耗資源比較平緩。
採用方案3時，4.0資料庫的pay.ORDERINFO_HIS還有2015年的分割槽，也就是說表空間P_2014與其他表空間有依賴關係。會報錯。
採用方案4、5時，與方案1類似，但比較複雜。不過在禁用索引的情況下，sql loader匯入資料的速度是其他方法無法比擬的。


考慮到簡單和快速要求，最終採用方案2，大概步驟為：




@analy40


生成匯出語句：
select 'expdp pay/******* directory=TEMP_DIR dumpfile='||
ds.segment_name||'_'||ds.partition_name||'.dmp logfile=expdp_'||
ds.segment_name||'_'||ds.partition_name||'.log tables='||
ds.segment_name||':'||ds.partition_name
from dba_segments ds
where ds.tablespace_name='PAY_DATA_P2014'
order by ds.segment_name,ds.partition_name;


expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201401.dmp logfile=expdp_ORDERINFO_HIS_P_201401.log tables=ORDERINFO_HIS:P_201401
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201402.dmp logfile=expdp_ORDERINFO_HIS_P_201402.log tables=ORDERINFO_HIS:P_201402
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201403.dmp logfile=expdp_ORDERINFO_HIS_P_201403.log tables=ORDERINFO_HIS:P_201403
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201404.dmp logfile=expdp_ORDERINFO_HIS_P_201404.log tables=ORDERINFO_HIS:P_201404
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201405.dmp logfile=expdp_ORDERINFO_HIS_P_201405.log tables=ORDERINFO_HIS:P_201405
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201406.dmp logfile=expdp_ORDERINFO_HIS_P_201406.log tables=ORDERINFO_HIS:P_201406
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201407.dmp logfile=expdp_ORDERINFO_HIS_P_201407.log tables=ORDERINFO_HIS:P_201407
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201408.dmp logfile=expdp_ORDERINFO_HIS_P_201408.log tables=ORDERINFO_HIS:P_201408
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201409.dmp logfile=expdp_ORDERINFO_HIS_P_201409.log tables=ORDERINFO_HIS:P_201409
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201410.dmp logfile=expdp_ORDERINFO_HIS_P_201410.log tables=ORDERINFO_HIS:P_201410
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201411.dmp logfile=expdp_ORDERINFO_HIS_P_201411.log tables=ORDERINFO_HIS:P_201411
expdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201412.dmp logfile=expdp_ORDERINFO_HIS_P_201412.log tables=ORDERINFO_HIS:P_201412




@analy30


為表空間增加資料檔案


禁用表的索引




匯入的語句：
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201402.dmp logfile=impdp_ORDERINFO_HIS_P_201402.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201402  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201403.dmp logfile=impdp_ORDERINFO_HIS_P_201403.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201403  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201404.dmp logfile=impdp_ORDERINFO_HIS_P_201404.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201404  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201405.dmp logfile=impdp_ORDERINFO_HIS_P_201405.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201405  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201406.dmp logfile=impdp_ORDERINFO_HIS_P_201406.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201406  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201407.dmp logfile=impdp_ORDERINFO_HIS_P_201407.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201407  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201408.dmp logfile=impdp_ORDERINFO_HIS_P_201408.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201408  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201409.dmp logfile=impdp_ORDERINFO_HIS_P_201409.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201409  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201410.dmp logfile=impdp_ORDERINFO_HIS_P_201410.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201410  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201411.dmp logfile=impdp_ORDERINFO_HIS_P_201411.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201411  TABLE_EXISTS_ACTION=append
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201412.dmp logfile=impdp_ORDERINFO_HIS_P_201412.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201412  TABLE_EXISTS_ACTION=append






以下操作是新建表ORDERINFO_HIS_201401,表空間指定pay_data_p2014，只匯入資料，然後使用分割槽交換技術將該分割槽插入。




建立表：
create  table pay.ORDERINFO_HIS_201401 
as select * from pay.ORDERINFO_HIS where 1=2;


匯入資料：
impdp pay/*********** directory=TEMP_DIR dumpfile=ORDERINFO_HIS_P_201401.dmp logfile=impdp_ORDERINFO_HIS_P_201401.log remap_table=ORDERINFO_HIS:ORDERINFO_HIS_201401 TABLE_EXISTS_ACTION=append


修改表空間屬性：
alter  table pay.ORDERINFO_HIS  move tablespace pay_data_p2014;


由於兩個表中存在主鍵衝突的資料，且em_tr_tradeinfo_his中的較舊，先將這部分資料刪除：
create table pay.ORDERINFO_HIS_201401_tmp 
as select * from pay.ORDERINFO_HIS_201401 where trade_sno<'EM0114';


select * from pay.ORDERINFO_HIS where trade_sno in 
(
select trade_sno from pay.ORDERINFO_HIS_201401_tmp 
)
union all
select * from pay.ORDERINFO_HIS_201401_tmp ;


delete from pay.ORDERINFO_HIS where trade_sno in 
(
select trade_sno from pay.ORDERINFO_HIS_201401_tmp 
);


嘗試進行分割槽交換：
SQL> alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes;
 
alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes
 
ORA-14130: UNIQUE 約束條件在 ALTER TABLE EXCHANGE PARTITION 中不匹配
 
這是由於pay.ORDERINFO_HIS_201401中未建立主鍵索引導致的，建立唯一索引：
 
SQL> create unique index idx_TRADEINFO_HIS_201401 on pay.ORDERINFO_HIS_201401(trade_sno) tablespace pay_data_p2013;
 
Index created
 
SQL> alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes;
 
alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes
 
ORA-14130: UNIQUE 約束條件在 ALTER TABLE EXCHANGE PARTITION 中不匹配
 
仍然報錯，需要建立主鍵：
SQL> alter table pay.ORDERINFO_HIS_201401 add constraint pk_tradeinfo_his_201401  primary key(trade_sno) using index;
 
Table altered
 


SQL> alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes;
 
alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes
 
ORA-14099: 表中不是所有行都符合所指定的分割槽


這個報錯是由於pay.ORDERINFO_HIS_201401表中存在不符合pay.ORDERINFO_HIS的p_201401分割槽範圍的資料，


先將這部分資料插入，然後刪除：
SQL> insert into pay.ORDERINFO_HIS
  2  select * from pay.ORDERINFO_HIS_201401 where submit_date<to_date('20140101','yyyymmdd');
 
4 rows inserted
 
SQL> commit;
 
Commit complete


SQL> delete from pay.ORDERINFO_HIS_201401 where submit_date<to_date('20140101','yyyymmdd');
 
4 rows deleted


再次嘗試交換分割槽：
SQL> alter table pay.ORDERINFO_HIS exchange partition p_201401 with table pay.ORDERINFO_HIS_201401 update indexes;
 
Table altered




同樣的方法處理後續分割槽：


create  table pay.ORDERINFO_HIS_201402 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201403 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201404 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201405 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201406 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201407 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201408 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201409 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201410 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201411 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;
create  table pay.ORDERINFO_HIS_201412 tablespace pay_data_p2014 as select * from pay.ORDERINFO_HIS where 1=2;




alter table pay.ORDERINFO_HIS_201402 add constraint pk_tradeinfo_his_201402  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201403 add constraint pk_tradeinfo_his_201403  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201404 add constraint pk_tradeinfo_his_201404  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201405 add constraint pk_tradeinfo_his_201405  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201406 add constraint pk_tradeinfo_his_201406  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201407 add constraint pk_tradeinfo_his_201407  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201408 add constraint pk_tradeinfo_his_201408  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201409 add constraint pk_tradeinfo_his_201409  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201410 add constraint pk_tradeinfo_his_201410  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201411 add constraint pk_tradeinfo_his_201411  primary key(trade_sno) using index tablespace pay_data_p2013;
alter table pay.ORDERINFO_HIS_201412 add constraint pk_tradeinfo_his_201412  primary key(trade_sno) using index tablespace pay_data_p2013;




alter table pay.ORDERINFO_HIS exchange partition p_201402 with table pay.ORDERINFO_HIS_201402 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201403 with table pay.ORDERINFO_HIS_201403 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201404 with table pay.ORDERINFO_HIS_201404 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201405 with table pay.ORDERINFO_HIS_201405 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201406 with table pay.ORDERINFO_HIS_201406 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201407 with table pay.ORDERINFO_HIS_201407 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201408 with table pay.ORDERINFO_HIS_201408 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201409 with table pay.ORDERINFO_HIS_201409 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201410 with table pay.ORDERINFO_HIS_201410 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201411 with table pay.ORDERINFO_HIS_201411 update indexes;
alter table pay.ORDERINFO_HIS exchange partition p_201412 with table pay.ORDERINFO_HIS_201412 update indexes;


最後刪除臨時建立的表：
drop table pay.ORDERINFO_HIS_201401;
drop table pay.ORDERINFO_HIS_201402;
drop table pay.ORDERINFO_HIS_201403;
drop table pay.ORDERINFO_HIS_201404;
drop table pay.ORDERINFO_HIS_201405;
drop table pay.ORDERINFO_HIS_201406;
drop table pay.ORDERINFO_HIS_201407;
drop table pay.ORDERINFO_HIS_201408;
drop table pay.ORDERINFO_HIS_201409;
drop table pay.ORDERINFO_HIS_201410;
drop table pay.ORDERINFO_HIS_201411;
drop table pay.ORDERINFO_HIS_201412;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-1817710/，如需轉載，請註明出處，否則將追究法律責任。