Migrate historical data using Partition Table partition exchange

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
Table space transfer more: http://blog.csdn.net/tanqingru/article/category/1138527


II. 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 transfer: More About tablespace transfer: http://blog.csdn.net/bamuta/article/category/1138527


Verify the self-contained tablespace:
SQL> exec dbms_tts.transport_set_check ('Tan _ 2013_9 ', true );


PL/SQL procedure successfully completed.


SQL> select * From transport_set_violations;

No rows selected

In addition, the character set of both databases must be consistent during tablespace transmission.

Export without data:

SQL> alter tablespace tan_2013_9 read only;

[[Email protected] ~] $ Exp \ 'sys/Oracle AS sysdba \ 'tablespaces = tan_2013_9 transport_tablespace = y file = exp_tan_2013_9.dmp

Copy an object
[[Email protected] ~] $ SCP exp_tan_2013_9.dmp 192.168.114.174:/home/Oracle/
[[Email protected] ~] $ SCP/data01/Qing/datafile/o1_mf_tan_2013_9tht2cgh _. DBF 192.168.114.174:/data01/vm603/vm603/datafile/

Import metadata to the target database
[[Email protected] ~] $ Imp \ 'sys/Oracle AS sysdba \ 'transport_tablespace = y file = exp_tan_2013_9.dmp log = imp. log tablespaces = tan_2013_9 datafiles = '/data01/vm603/vm603/datafile/o1_mf_tan_2013_9tht2cgh _. dbf'


Read and Write the tablespace in both databases
SQL> alter tablespace tan_2013_9 read write;

4. Swap partitions for the target database Add partitions to the target database:
SQL> ALTER TABLE tan add partition tan_2013_9 values less than (to_date ('2017-10-01 ', 'yyyy-mm-dd') tablespace tan_2013_9;


Table altered.


SQL> select count (*) from Tan partition (tan_2013_9 );


Count (*)
----------
0


Perform a partition exchange in the target database.
SQL> ALTER TABLE tan exchange partition tan_2013_9 with table tmp_tan_2013_9 including indexes with validation;


Table altered.


Check

SQL>  select count(*) from tan partition(tan_2013_9);  COUNT(*)----------       833SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------TAN                            TAN_2013_8                     TAN_2013_8TAN                            TAN_2013_7                     TAN_2013_7TAN                            TAN_2013_9                     TAN_2013_9SQL> select index_name,partition_name,tablespace_name from user_ind_partitions;INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------IND_TAN                        IND_TAN_2013_8                 TAN_2013_8IND_TAN                        IND_TAN_2013_7                 TAN_2013_7IND_TAN                        TAN_2013_9                     TAN_2013_9

5. Delete the migrated partitions from the source database:

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------TAN                            TAN_2014_3                     TAN_2014_3TAN                            TAN_2014_2                     TAN_2014_2TAN                            TAN_2014_4                     TAN_2014_4TAN                            TAN_2014_5                     TAN_2014_5TAN                            TAN_2014_6                     TAN_2014_6TAN                            TAN_2014_7                     TAN_2014_7TAN                            TAN_2014_8                     TAN_2014_8TAN                            TAN_2013_10                    TAN_2013_10TAN                            TAN_2013_11                    TAN_2013_11TAN                            TAN_2013_12                    TAN_2013_12TAN                            TAN_2014_1                     TAN_2014_1TAN                            TAN_2013_9                     USERS12 rows selected.SQL> <strong>alter table tan drop partition tan_2013_9;</strong>Table altered.SQL>  select table_name,partition_name,tablespace_name from user_tab_partitions;TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------TAN                            TAN_2014_3                     TAN_2014_3TAN                            TAN_2014_2                     TAN_2014_2TAN                            TAN_2014_4                     TAN_2014_4TAN                            TAN_2014_5                     TAN_2014_5TAN                            TAN_2014_6                     TAN_2014_6TAN                            TAN_2014_7                     TAN_2014_7TAN                            TAN_2014_8                     TAN_2014_8TAN                            TAN_2013_10                    TAN_2013_10TAN                            TAN_2013_11                    TAN_2013_11TAN                            TAN_2013_12                    TAN_2013_12TAN                            TAN_2014_1                     TAN_2014_1