Oracle用分区表分区交换做历史数据迁移

<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 loop
insert 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(*)
----------
      5000


SQL> 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(*)
----------
         0
SQL> select count(*) from tmp_tan_2013_9;
  COUNT(*)
----------
       833
SQL> select partition_name,tablespace_name from dba_segments
     where segment_name in ('TAN','IND_TAN');


PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TAN_2014_8                     TAN_2014_8
TAN_2014_7                     TAN_2014_7
TAN_2014_6                     TAN_2014_6
TAN_2014_5                     TAN_2014_5
TAN_2014_4                     TAN_2014_4
TAN_2014_3                     TAN_2014_3
TAN_2014_2                     TAN_2014_2
TAN_2014_1                     TAN_2014_1
TAN_2013_12                    TAN_2013_12
TAN_2013_11                    TAN_2013_11
TAN_2013_10                    TAN_2013_10
IND_TAN_2014_8                 TAN_2014_8
IND_TAN_2014_7                 TAN_2014_7
IND_TAN_2014_6                 TAN_2014_6
IND_TAN_2014_5                 TAN_2014_5
IND_TAN_2014_4                 TAN_2014_4
IND_TAN_2014_3                 TAN_2014_3
IND_TAN_2014_2                 TAN_2014_2
IND_TAN_2014_1                 TAN_2014_1
IND_TAN_2013_12                TAN_2013_12
IND_TAN_2013_11                TAN_2013_11
IND_TAN_2013_10                TAN_2013_10


22 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;

[oracle@OEL63 ~]$ exp \‘sys/oracle as sysdba\‘ tablespaces=tan_2013_9 transport_tablespace=y file=exp_tan_2013_9.dmp 

复制文件
[oracle@OEL63 ~]$ scp exp_tan_2013_9.dmp 192.168.114.174:/home/oracle/
[oracle@OEL63 ~]$ scp /data01/qing/QING/datafile/o1_mf_tan_2013_9tht2cgh_.dbf 192.168.114.174:/data01/vm603/VM603/datafile/

在目标库导入元数据
[oracle@vm603 ~]$ 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(*)
----------
       833


SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TAN                            TAN_2013_8                     TAN_2013_8
TAN                            TAN_2013_7                     TAN_2013_7
TAN                            TAN_2013_9                     TAN_2013_9


SQL> 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_8
IND_TAN                        IND_TAN_2013_7                 TAN_2013_7
IND_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_3
TAN                            TAN_2014_2                     TAN_2014_2
TAN                            TAN_2014_4                     TAN_2014_4
TAN                            TAN_2014_5                     TAN_2014_5
TAN                            TAN_2014_6                     TAN_2014_6
TAN                            TAN_2014_7                     TAN_2014_7
TAN                            TAN_2014_8                     TAN_2014_8
TAN                            TAN_2013_10                    TAN_2013_10
TAN                            TAN_2013_11                    TAN_2013_11
TAN                            TAN_2013_12                    TAN_2013_12
TAN                            TAN_2014_1                     TAN_2014_1
TAN                            TAN_2013_9                     USERS


12 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_3
TAN                            TAN_2014_2                     TAN_2014_2
TAN                            TAN_2014_4                     TAN_2014_4
TAN                            TAN_2014_5                     TAN_2014_5
TAN                            TAN_2014_6                     TAN_2014_6
TAN                            TAN_2014_7                     TAN_2014_7
TAN                            TAN_2014_8                     TAN_2014_8
TAN                            TAN_2013_10                    TAN_2013_10
TAN                            TAN_2013_11                    TAN_2013_11
TAN                            TAN_2013_12                    TAN_2013_12
TAN                            TAN_2014_1                     TAN_2014_1

Oracle用分区表分区交换做历史数据迁移

标签：