Oracle Partition Table Partitioning exchange for historical data migrations

One.

Description

Some of the table data in the OLTP library is large and there is a constant amount of data added every month. Because historical data is not visited in this library, it is analyzed in 1 other OLAP libraries. Therefore, the historical data is migrated to the OLAP library. Operations on such historical data migrations. A better approach is to use a partitioned table for this table. After partitioning by time, the partition can be migrated. With partition switching and tablespace transfer, it's easy to finish. And the performance impact is very small.


Many other things about partitioned tables: http://blog.csdn.net/tanqingru/article/category/1397435
There are many other things about table space: http://blog.csdn.net/tanqingru/article/category/1138527


Two.

Example: The entire process is partitioned in an OLTP library. It is then migrated to the OLAP library through tablespace transport. Finally, do another partition exchange.

1. Create the required environment.

OLTP Library Environment Readiness:

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 Prod Uctiontns 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 si Ze 5m autoextend on;create tablespace tan_2013_10 datafile size 5m autoextend on;create tablespace tan_2013_11 datafile si Ze 5m autoextend on;create tablespace tan_2013_12 datafile size 5m autoextend on;create tablespace tan_2014_1 datafile siz E 5m autoextend on;create tablespace tan_2014_2 datafile size 5m autoextend on;create tablespaceTan_2014_3 datafile size 5m autoextend on;create tablespace tan_2014_4 datafile size 5m autoextend on;create tablespace ta N_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> Crea Te table tan (t_id number), T_name varchar2 (+), t_date date) partition by range (t_date) (Partition Tan_2013_9 values les S 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_2 013_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.    

OLAP Library Environment preparation

<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), T_name varchar2 (+), 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 me 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. Partition switching The thing to do today is to migrate September 2013 data.

Create a temporary table:
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;

Verify:
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 (' TA N ', ' 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: Many other content about tablespace transfers: http://blog.csdn.net/bamuta/article/category/1138527


Verify that the table space is self-contained:
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 table space transfer requires the character set of both ends of the library consistent.

Export No 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

Copying Files
[Email protected] ~]$ SCP exp_tan_2013_9.dmp 192.168.114.174:/home/oracle/
[Email protected] ~]$ scp/data01/qing/qing/datafile/o1_mf_tan_2013_9tht2cgh_.dbf 192.168.114.174:/data01/vm603/ vm603/datafile/

importing metadata in the destination library
[[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 '


The table space for both ends of the library is read write
Sql> alter tablespace Tan_2013_9 Read write;

4. Target repository and partition switching The target library adds a partition to the table:
Sql> ALTER TABLE Tan Add partition Tan_2013_9 values less than (to_date (' 2013-10-01 ', ' yyyy-mm-dd ')) tablespace tan_2013 _9;


Table altered.


Sql> Select COUNT (*) from Tan partition (TAN_2013_9);


COUNT (*)
----------
0


do a partition exchange in the target repository.

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 partition from the Source library:

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 S elected. 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

Oracle Partition Table Partitioning exchange for historical data migrations