一个利用传输表空间和分区交换技术进行数据迁移的案例

1. 案例背景
Product：生产交易 数据库 ，存放当月交易数据。其中，关键交易表按天分区。
DW：交易历史数据库（数据仓库），存放历史交易数据，其中，关键交易表是按月分区。
功能需求：在每个月末，将Product的交易表数据全部移植到DW中。
2. 实施过程
--Product中关键交易表Sales的定义如下：
create table sales (invoice_no number, sale_year int, sale_month int, sale_day int)
    partition by range ( sale_day )
         (partition p1 values less than (2) tablespace ts_p1,
          partition p2 values less than (3) tablespace ts_p2,
          partition p3 values less than (4) tablespace ts_p3,
          ... ...
          partition p31 values less than (32) tablespace ts_p31);
即每天的交易数据都单独放在一个partition和表空间中。
--DW中关键交易表Sales的定义如下：
create table sales_history (invoice_no number, sale_year int, sale_month int, sale_day int)
    partition by range ( sale_year,sale_month,sale_day )
         (partition jan2010 values less than (2010,2,1) tablespace ts_jan2010,
          partition feb2010 values less than (2010,3,1) tablespace ts_feb2010,
          partition mar2010 values less than (2010,4,1) tablespace ts_mar2010,
          ... ...
          partition sep2010 values less than (2010,10,1) tablespace ts_sep2010);
即每月的交易数据都单独放在一个partition和表空间中。
--在DW中用于 分区交换 的临时表tmp定义如下：
create table tmp(invoice_no number, sale_year int, sale_month int, sale_day int)
--在每个月的月末，进行如下操作：
1）检查表空间的自包含性
在Product数据库中，以sys用户执行：
execute dbms_tts.transport_set_check('ts_p1','ts_p2',...,'ts_p31',TRUE);
完成上述操作之后，再查询以下视图：
select * from transport_set_violations;
如果没有返回值，则表示表空间是自包含的。
2）将表空间设为只读
alter tablespace ts_p1 read only;
......
alter tablespace ts_p31 read only;
3）用exp生成 传输表空间 的dmp文件：
transport_tablespace = y
tablespaces = (ts_p1,......,ts_p31)
trigger=y
constrains=n
grants=n
file=tts.dmp
4）传输dmp文件和数据文件
把上一步生成的tts.dmp及表空间对应的所有数据文件都传输到DW服务器上。
5）将Product的表空间 恢复 为读写模式
alter tablespace ts_p1 read write;
......
alter tablespace ts_p31 read write;
6）用imp导入dmp文件
transport_tablespace = y
datafiles=('/db/p1.dbf',......)
tts_owner = (...)
fromuser = (...)
touser=(...)
file=tts.dmp
7）将DW中sales分区表合并为一个分区 p1
alter table sales merge partitions p31,p30 into partition p30;
......
alter table sales merge partitions p2,p1 into partition p1;
8）将sales表交换至临时表tmp中
alter table sales exchange partition p1 with table tmp;
9）在DW中为sales_history添加一个新的当月的partition
alter table sales_history add partition oct2010 values less than (2010,11,1) tablespace ts_oct2010);
10) 将临时表tmp交换至sales_history新添加的partition中
alter table sales_history exchange partition oct2010 with table tmp;
11）后续工作
删除临时表，删除过期的表空间等。
3. 方案评估
在整个数据迁移的过程中，最耗时间的操作时文件传输过程，取决于网络宽带。而在数据库层面，所有的操作几乎都是数据字典的操作，非常的高效。