Oracle用分區表分區交換做曆史資料移轉

標籤：

一。

說明：

     OLTP庫中有些表資料量大，且每月有持續的大量資料添加。因為曆史資料在此庫中不再做訪問，而是在另1個OLAP庫中做分析。所以會對曆史資料移轉至OLAP庫中。對這樣的曆史資料移轉的操作。較好的辦法是該表採用分區表。按時間分區後，能夠對分區進行遷移。通過分區交換和資料表空間傳輸會非常easy完畢。並且效能上影響非常小。


關於分區表很多其它內容：    http://blog.csdn.net/tanqingru/article/category/1397435 
關於資料表空間傳很多其它內容： http://blog.csdn.net/tanqingru/article/category/1138527 


二。

執行個體：整個過程是在OLTP庫做分區交換。然後通過資料表空間傳輸遷移至OLAP庫。最後再做一次分區交換就可以。

1.創造須要的環境。

OLTP庫環境準備：

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.   

OLAP庫環境準備

<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.分區交換   如今要做的事是遷移2013年9月份資料。

建立個暫時表：
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);


分區交換。
SQL> alter table tan exchange partition tan_2013_9 

    with table tmp_tan_2013_9 including indexes with validation;

驗證：
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. 資料表空間傳輸：很多其它關於資料表空間傳輸的內容：http://blog.csdn.net/bamuta/article/category/1138527


驗證資料表空間的自包括：
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

另外資料表空間傳輸須要兩端庫的字元集一致。

匯出無資料：

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 

複製檔案
[[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/

在目標庫匯入中繼資料
[[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 write 
SQL>  alter tablespace tan_2013_9 read write;

4.目標庫再做分區交換 目標庫對就表添加分區：
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


在目標庫做一次分區交換。

SQL> alter table tan exchange partition tan_2013_9 with table tmp_tan_2013_9 including indexes with validation;


Table altered.


檢查

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.源庫刪掉已經遷移走的分區：

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

Oracle用分區表分區交換做曆史資料移轉