两个历史表的数据合并

目的：清理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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1817710/