Oracle uses Partition Table partition exchange for historical data migration

I. Note: Some tables in the OLTP database have a large amount of data and a large amount of data is continuously increased every month. Because historical data is not accessed in this database, it is analyzed in another OLAP database, therefore, historical data will be migrated to the OLAP database. A better way to migrate this type of historical data is to use a partitioned table. After partitioning by time, You can migrate the partition. Data can be easily transmitted through partition swap and tablespace, with little impact on performance.

More about partition tables: http://blog.csdn.net/tanqingru/article/category/1397435
For more information about table space transfer, visit http://blog.csdn.net/tanqingru/article/category/11108272. Instance: partitions are exchanged in the OLTP database. Then, the data is migrated to the OLAP database through the tablespace transmission, and the partition exchange is the last time. 1. create the required environment. Prepare the OLTP database environment:

SQL> conn /as sysdbaConnected.SQL> select * from V$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> show parameter db_create_file_destNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  string      /data01/qingcreate 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/tanSQL> 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_tan_2013_12 tablespace tan_2013_12,partition ind_tan_2014_1  tablespace tan_2014_1,partition ind_tan_2014_2  tablespace tan_2014_2,partition ind_tan_2014_3 tablespace tan_2014_3,partition ind_tan_2014_4 tablespace tan_2014_4,partition ind_tan_2014_5 tablespace tan_2014_5,partition ind_tan_2014_6 tablespace tan_2014_6,partition ind_tan_2014_7 tablespace tan_2014_7,partition ind_tan_2014_8 tablespace tan_2014_8 );begin for i in 1.. 10000 loopif( mod(i,12)+1 <=8) theninsert into tan values(i,'tan'||i,to_date('2014-'||(mod(i,12)+1)||'-01','yyyy-mm-dd'));else insert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,12)+1)||'-01','yyyy-mm-dd'));end if;end loop;commit;end;/SQL> select count(*) from tan partition(tan_2013_12) ;  COUNT(*)----------       833SQL> select count(*) from tan partition(tan_2013_9) ;  COUNT(*)----------       833SQL> select count(*) from tan partition(tan_2014_8) ;  COUNT(*)----------       833SQL> select count(*) from tan partition(tan_2014_1) ;  COUNT(*)----------       833SQL> select partition_name,tablespace_name from dba_segments   where segment_name in ('TAN','IND_TAN');PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------TAN_2014_8                     TAN_2014_8TAN_2014_7                     TAN_2014_7TAN_2014_6                     TAN_2014_6TAN_2014_5                     TAN_2014_5TAN_2014_4                     TAN_2014_4TAN_2014_3                     TAN_2014_3TAN_2014_2                     TAN_2014_2TAN_2014_1                     TAN_2014_1TAN_2013_9                     TAN_2013_9TAN_2013_12                    TAN_2013_12TAN_2013_11                    TAN_2013_11TAN_2013_10                    TAN_2013_10IND_TAN_2014_8                 TAN_2014_8IND_TAN_2014_7                 TAN_2014_7IND_TAN_2014_6                 TAN_2014_6IND_TAN_2014_5                 TAN_2014_5IND_TAN_2014_4                 TAN_2014_4IND_TAN_2014_3                 TAN_2014_3IND_TAN_2014_2                 TAN_2014_2IND_TAN_2014_1                 TAN_2014_1IND_TAN_2013_9                 TAN_2013_9IND_TAN_2013_12                TAN_2013_12IND_TAN_2013_11                TAN_2013_11IND_TAN_2013_10                TAN_2013_1024 rows selected.   

Prepare the OLAP database environment

<span style="font-family: Arial, Helvetica, sans-serif; font-size: 12px;">create tablespace tan_2013_7 datafile size 5m autoextend on;</span>

create tablespace tan_2013_8 datafile size 5m autoextend on; create table tan(t_id number(10),t_name varchar2(100),t_date date )partition by range(t_date)(partition tan_2013_7 values less than(to_date('2013-08-01','yyyy-mm-dd')) tablespace tan_2013_7,partition tan_2013_8 values less than(to_date('2013-09-01','yyyy-mm-dd')) tablespace tan_2013_8);create index ind_tan on tan(t_date) local(partition ind_tan_2013_7  tablespace tan_2013_7,partition ind_tan_2013_8 tablespace tan_2013_8);begin for i in 1.. 10000 loopinsert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,2)+7)||'-01','yyyy-mm-dd'));end loop;commit;end;/SQL> select count(*) from tan partition(tan_2013_8);  COUNT(*)----------      5000SQL> select count(*) from tan partition(tan_2013_7);  COUNT(*)----------      5000

2. What we need to do now for partition exchange is to migrate data for January.

Create temporary tables:
SQL> create table tmp_tan_2013_9 as select * from tan where 1 = 2;
SQL> create index ind_tmp_tan_2013_9 on tmp_tan_2013_9 (t_date );


Partition switching.
SQL> alter table tan exchange partition tan_2013_9

With table tmp_tan_2013_9 including indexes with validation;

Verification:
SQL> select count (*) from tan partition (tan_2013_9 );

  COUNT(*)----------         0SQL> select count(*) from tmp_tan_2013_9;  COUNT(*)----------       833SQL> select partition_name,tablespace_name from dba_segments     where segment_name in ('TAN','IND_TAN');PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------TAN_2014_8                     TAN_2014_8TAN_2014_7                     TAN_2014_7TAN_2014_6                     TAN_2014_6TAN_2014_5                     TAN_2014_5TAN_2014_4                     TAN_2014_4TAN_2014_3                     TAN_2014_3TAN_2014_2                     TAN_2014_2TAN_2014_1                     TAN_2014_1TAN_2013_12                    TAN_2013_12TAN_2013_11                    TAN_2013_11TAN_2013_10                    TAN_2013_10IND_TAN_2014_8                 TAN_2014_8IND_TAN_2014_7                 TAN_2014_7IND_TAN_2014_6                 TAN_2014_6IND_TAN_2014_5                 TAN_2014_5IND_TAN_2014_4                 TAN_2014_4IND_TAN_2014_3                 TAN_2014_3IND_TAN_2014_2                 TAN_2014_2IND_TAN_2014_1                 TAN_2014_1IND_TAN_2013_12                TAN_2013_12IND_TAN_2013_11                TAN_2013_11IND_TAN_2013_10                TAN_2013_1022 rows selected.   

3. tablespace transmission: