ITPub博客

首页 > 数据库 > Oracle > 两个历史表的数据合并

两个历史表的数据合并

原创 Oracle 作者:to_be_dba 时间:2015-10-28 20:38:23 0 删除 编辑
目的:清理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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    392005