一。说明：
OLTP库中有些表数据量大，且每月有持续的大量数据增加，由于历史数据在此库中不再做访问，而是在另1个OLAP库中做分析，所以会对历史数据迁移至OLAP库中。对这种历史数据迁移的操作，较好的办法是该表采用分区表。按时间分区后，可以对分区进行迁移。通过分区交换和表空间传输会很容易完成，而且性能上影响很小。关于表空间传更多内容： http://blog.csdn.net/tanqingru/article/category/1138527
二。实例：
整个过程是在OLTP库做分区交换。然后通过表空间传输迁移至OLAP库，最后再做一次分区交换即可。1.创造需要的环境。
OLTP库环境准备：
SQL> conn /as sysdba Connected. SQL> select * from V$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /data01/qing create tablespace tan_2013_9 datafile size 5m autoextend on; create tablespace tan_2013_10 datafile size 5m autoextend on; create tablespace tan_2013_11 datafile size 5m autoextend on; create tablespace tan_2013_12 datafile size 5m autoextend on; create tablespace tan_2014_1 datafile size 5m autoextend on; create tablespace tan_2014_2 datafile size 5m autoextend on; create tablespace tan_2014_3 datafile size 5m autoextend on; create tablespace tan_2014_4 datafile size 5m autoextend on; create tablespace tan_2014_5 datafile size 5m autoextend on; create tablespace tan_2014_6 datafile size 5m autoextend on; create tablespace tan_2014_7 datafile size 5m autoextend on; create tablespace tan_2014_8 datafile size 5m autoextend on; conn tan/tan SQL> create table tan (t_id number(10), t_name varchar2(100), t_date date ) partition by range(t_date) (partition tan_2013_9 values less than(to_date('2013-10-01','yyyy-mm-dd')) tablespace tan_2013_9, partition tan_2013_10 values less than(to_date('2013-11-01','yyyy-mm-dd')) tablespace tan_2013_10, partition tan_2013_11 values less than(to_date('2013-12-01','yyyy-mm-dd')) tablespace tan_2013_11, partition tan_2013_12 values less than(to_date('2014-01-01','yyyy-mm-dd')) tablespace tan_2013_12, partition tan_2014_1 values less than(to_date('2014-02-01','yyyy-mm-dd')) tablespace tan_2014_1, partition tan_2014_2 values less than(to_date('2014-03-01','yyyy-mm-dd')) tablespace tan_2014_2, partition tan_2014_3 values less than(to_date('2014-04-01','yyyy-mm-dd')) tablespace tan_2014_3, partition tan_2014_4 values less than(to_date('2014-05-01','yyyy-mm-dd')) tablespace tan_2014_4, partition tan_2014_5 values less than(to_date('2014-06-01','yyyy-mm-dd')) tablespace tan_2014_5, partition tan_2014_6 values less than(to_date('2014-07-01','yyyy-mm-dd')) tablespace tan_2014_6, partition tan_2014_7 values less than(to_date('2014-08-01','yyyy-mm-dd')) tablespace tan_2014_7, partition tan_2014_8 values less than(to_date('2014-09-01','yyyy-mm-dd')) tablespace tan_2014_8 ); create index ind_tan on tan(t_date) local ( partition ind_tan_2013_9 tablespace tan_2013_9, partition ind_tan_2013_10 tablespace tan_2013_10, partition ind_tan_2013_11 tablespace tan_2013_11, partition ind_t