在线过期数据迁移到离线数据库的案例

  特别说明：该案例引自谭老师的《让Oracle跑的更快2》。

  实验说明：

  该实验将用到在线数据库YFT1，离线数据库YFT2。

  实验操作：

一、分别在两个数据库中创建一个分区表，并为每个分区创建一个单独的表空间，以便于和临时表做分区交换。

  1.1、在数据库YFT1中：

 1 [oracle@node2 ~]$ env |grep ORA 2 ORACLE_SID=YFT1 3 ORACLE_BASE=/u01/app/oracle 4 ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 5 [oracle@node2 ~]$ sqlplus /nolog 6  7 SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 19 13:56:53 2012 8  9 Copyright (c) 1982, 2009, Oracle.  All rights reserved.10 11 SQL> conn /as sysdba12 Connected.13 SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/YFT1/';14 15 System altered.16 17 SQL> create tablespace ts_sales_2009_1 datafile size 50m autoextend on;18 19 Tablespace created.20 21 SQL> create tablespace ts_sales_2009_2 datafile size 50m autoextend on;22 23 Tablespace created.24 25 SQL> create tablespace ts_sales_2009_3 datafile size 50m autoextend on;26 27 Tablespace created.28 29 SQL> create tablespace ts_sales_2009_4 datafile size 50m autoextend on;30 31 Tablespace created.32 33 SQL> create tablespace ts_sales_2009_5 datafile size 50m autoextend on;34 35 Tablespace created.36 37 SQL> create tablespace ts_sales_2009_6 datafile size 50m autoextend on;38 39 Tablespace created.40 41 SQL> create tablespace ts_sales_2009_7 datafile size 50m autoextend on; 42 43 Tablespace created.44 45 SQL> create tablespace ts_sales_2009_8 datafile size 50m autoextend on; 46 47 Tablespace created.48 49 SQL> create tablespace ts_sales_2009_9 datafile size 50m autoextend on; 50 51 Tablespace created.52 53 SQL> create tablespace ts_sales_2009_10 datafile size 50m autoextend on; 54 55 Tablespace created.56 57 SQL> create tablespace ts_sales_2009_11 datafile size 50m autoextend on; 58 59 Tablespace created.60 61 SQL> create tablespace ts_sales_2009_12 datafile size 50m autoextend on; 62 63 Tablespace created.64 65 SQL> create tablespace ts_sales_2010_1 datafile size 50m autoextend on; 66 67 Tablespace created.

  其中第一条语句是设定一个数据文件存放路径，让Oracle自动在该路径下创建文件，并使用Oracle自己的方式为数据文件命名。

  接下来的语句就是为每个表分区创建一个独立的表空间，从表空间名字上就可以看出来。另外，同时为每个分区索引创建一个索引表空间。

 二、创建分区表，将表的每个分区放到自己对应的表空间上。下面是创建表的语句，包含13个分区，时间跨度为1年零1个月。将分别在YFT1和YFT2上操作。

  2.1、在YFT1中：

 1 SQL> create user jack identified by jack; 2  3 User created. 4  5 SQL> grant dba to jack; 6  7 Grant succeeded. 8  9 SQL> conn jack/jack;10 Connected.11 12 SQL> create table sale_data (sale_id number(5),salesmane_name varchar2(30),sales_amount number(10),sales_date date)13   2     partition by range(sales_date)14   3  (15   4     partition sales_2009_1 values less than(to_date('01/02/2009','DD/MM/YYYY')) tablespace ts_sales_2009_1, 16   5     partition sales_2009_2 values less than(to_date('01/03/2009','DD/MM/YYYY')) tablespace ts_sales_2009_2,      17   6     partition sales_2009_3 values less than(to_date('01/04/2009','DD/MM/YYYY')) tablespace ts_sales_2009_3,  18   7     partition sales_2009_4 values less than(to_date('01/05/2009','DD/MM/YYYY')) tablespace ts_sales_2009_4,19   8     partition sales_2009_5 values less than(to_date('01/06/2009','DD/MM/YYYY')) tablespace ts_sales_2009_5,20   9     partition sales_2009_6 values less than(to_date('01/07/2009','DD/MM/YYYY')) tablespace ts_sales_2009_6, 21  10     partition sales_2009_7 values less than(to_date('01/08/2009','DD/MM/YYYY')) tablespace ts_sales_2009_7,22  11     partition sales_2009_8 values less than(to_date('01/09/2009','DD/MM/YYYY')) tablespace ts_sales_2009_8, 23  12     partition sales_2009_9 values less than(to_date('01/10/2009','DD/MM/YYYY')) tablespace ts_sales_2009_9,  24  13     partition sales_2009_10 values less than(to_date('01/11/2009','DD/MM/YYYY')) tablespace ts_sales_2009_10, 25  14     partition sales_2009_11 values less than(to_date('01/12/2009','DD/MM/YYYY')) tablespace ts_sales_2009_11, 26  15     partition sales_2009_12 values less than(to_date('01/01/2010','DD/MM/YYYY')) tablespace ts_sales_2009_12, 27  16     partition sales_2010_1 values less than(to_date('01/02/2010','DD/MM/YYYY')) tablespace ts_sales_2010_128  17  );29 30 Table created.31 32 SQL> create index ind_sale_data on sale_data(sales_date)33   2    local34   3  (35   4    partition sales_2009_1 tablespace ts_sales_2009_1,36   5    partition sales_2009_2 tablespace ts_sales_2009_2,37   6    partition sales_2009_3 tablespace ts_sales_2009_3,38   7    partition sales_2009_4 tablespace ts_sales_2009_4,39   8    partition sales_2009_5 tablespace ts_sales_2009_5,40   9    partition sales_2009_6 tablespace ts_sales_2009_6, 41  10    partition sales_2009_7 tablespace ts_sales_2009_7,42  11    partition sales_2009_8 tablespace ts_sales_2009_8,43  12    partition sales_2009_9 tablespace ts_sales_2009_9,44  13    partition sales_2009_10 tablespace ts_sales_2009_10,45  14    partition sales_2009_11 tablespace ts_sales_2009_11,46  15    partition sales_2009_12 tablespace ts_sales_2009_12,47  16    partition sales_2010_1 tablespace ts_sales_2010_148  17  );49 50 Index created.51 52 SQL> select partition_name,tablespace_name from user_segments53   2    where segment_name in ('SALE_DATA','IND_SALE_DATA');54 55 PARTITION_NAME               TABLESPACE_NAME56 ------------------------------ ------------------------------57 SALES_2009_1               TS_SALES_2009_158 SALES_2009_10               TS_SALES_2009_1059 SALES_2009_11               TS_SALES_2009_1160 SALES_2009_12               TS_SALES_2009_1261 SALES_2009_2               TS_SALES_2009_262 SALES_2009_3               TS_SALES_2009_363 SALES_2009_4               TS_SALES_2009_464 SALES_2009_5               TS_SALES_2009_565 SALES_2009_6               TS_SALES_2009_666 SALES_2009_7               TS_SALES_2009_767 SALES_2009_8               TS_SALES_2009_868 69 PARTITION_NAME               TABLESPACE_NAME70 ------------------------------ ------------------------------71 SALES_2009_9               TS_SALES_2009_972 SALES_2010_1               TS_SALES_2010_173 SALES_2009_1               TS_SALES_2009_174 SALES_2009_10               TS_SALES_2009_1075 SALES_2009_11               TS_SALES_2009_1176 SALES_2009_12               TS_SALES_2009_1277 SALES_2009_2               TS_SALES_2009_278 SALES_2009_3               TS_SALES_2009_379 SALES_2009_4               TS_SALES_2009_480 SALES_2009_5               TS_SALES_2009_581 SALES_2009_6               TS_SALES_2009_682 83 PARTITION_NAME               TABLESPACE_NAME84 ------------------------------ ------------------------------85 SALES_2009_7               TS_SALES_2009_786 SALES_2009_8               TS_SALES_2009_887 SALES_2009_9               TS_SALES_2009_988 SALES_2010_1               TS_SALES_2010_189 90 26 rows selected.

  在上面操作中为分区表创建了分区索引，每个分区索引放在各自对应的分区表空间上。

  从上面的查询结果可以看到，每个分区和分区索引都创建在各自的表空间上了。

   2.2、重要说明：

   下面我们就要为导出分区表空间做准备了。

   比如，现在我们要导出的分区是SALES_2009_1，它对应的表空间为TS_SALES_2009_1。

   需要注意的是，当以表空间传递的方式导出表空间时，我们不能直接导出分区所在的表空间，这在Oracle里面是不允许的。

   使用表空间传递的方式导出数据，至少需要满足下面的几个条件：

   a、源和目标数据库必须使用相同的字符集和国家字符集；

   b、目标数据库不能包含同名的表空间；

   c、表空间上的对象必须是自包含的。

   前两个条件比较好理解，我们来看第三个条件,它是什么意思呢？

   自包含的意思是，对于需要进行传递的表空间上的对象，它不会引用到表空间之外的对象。比如：

   a、索引在这个表空间上，但是它引用到的表在需要传递的表空间以外的其他表空间上。

   b、分区表的部分分区在需要传递的表空间以外的其他表空间上。

   c、完整性约束的参考对象在需要传递的表空间以外的其他表空间上。

   d、表中包含的LOB对象存储在需要传递的表空间以外的其他表空间上。

   对于这个案例来说，每个表空间只存储一个表分区，这样我们就不能对单个表空间进行表空间传递操作了，因为它上面存储的对象不是自包含的。

三、验证表空间是否符合表空间传递的方式导出。

 1 SQL> conn /as sysdba 2 Connected. 3 SQL> exec dbms_tts.transport_set_check('TS_SALES_2009_1',TRUE); 4  5 PL/SQL procedure successfully completed. 6  7 SQL> select * from transport_set_violations; 8  9 VIOLATIONS10 --------------------------------------------------------------------------------11 ORA-39921: Default Partition (Table) Tablespace USERS for SALE_DATA not containe12 d in transportable set.13 14 ORA-39901: Partitioned table JACK.SALE_DATA is partially contained in the transp15 ortable set.

   上面的信息显示了我们要传递的表空间有两点不符合传递要求：

   a、分区表的默认表空间没有包含在传递的表空间。

   b、表空间还有其他的分区，没有在要传递的表空间上。

   基于以上两点原因，我们不能直接对分区的表空间进行移植，而这种数据存储的设计是合理的，所以我们需要考虑使用其他的方式来解决这个问题。

   星域的是，我们找到了一种方法，那就是分区交换。它的方法是这样的：

   a、创建一个临时表；

   b、在临时表上创建索引；

   c、将需要导出的分区数据和分区索引与临时表进行分区交换；

   d、将临时表和索引所在的表空间导出。

四、分区交换的具体操作：

   我们看到，最初的时候，各个分区对应的各自的表空间上。

 1 SQL>   select partition_name,tablespace_name,segment_type from user_segments  2   2       where segment_name='SALE_DATA'; 3  4 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE 5 ------------------------------ ------------------------------ ------------------ 6 SALES_2009_1               TS_SALES_2009_1              TABLE PARTITION 7 SALES_2009_10               TS_SALES_2009_10           TABLE PARTITION 8 SALES_2009_11               TS_SALES_2009_11           TABLE PARTITION 9 SALES_2009_12               TS_SALES_2009_12           TABLE PARTITION10 SALES_2009_2               TS_SALES_2009_2              TABLE PARTITION11 SALES_2009_3               TS_SALES_2009_3              TABLE PARTITION12 SALES_2009_4               TS_SALES_2009_4              TABLE PARTITION13 SALES_2009_5               TS_SALES_2009_5              TABLE PARTITION14 SALES_2009_6               TS_SALES_2009_6              TABLE PARTITION15 SALES_2009_7               TS_SALES_2009_7              TABLE PARTITION16 SALES_2009_8               TS_SALES_2009_8              TABLE PARTITION17 18 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE19 ------------------------------ ------------------------------ ------------------20 SALES_2009_9               TS_SALES_2009_9              TABLE PARTITION21 SALES_2010_1               TS_SALES_2010_1              TABLE PARTITION22 23 13 rows selected.

  每个分区索引也和分区存放在同一个表空间上。

 1 SQL> select partition_name,tablespace_name,segment_type from user_segments                      2     where segment_name='IND_SALE_DATA'; 2  3 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE 4 ------------------------------ ------------------------------ ------------------ 5 SALES_2009_1               TS_SALES_2009_1              INDEX PARTITION 6 SALES_2009_10               TS_SALES_2009_10           INDEX PARTITION 7 SALES_2009_11               TS_SALES_2009_11           INDEX PARTITION 8 SALES_2009_12               TS_SALES_2009_12           INDEX PARTITION 9 SALES_2009_2               TS_SALES_2009_2              INDEX PARTITION10 SALES_2009_3               TS_SALES_2009_3              INDEX PARTITION11 SALES_2009_4               TS_SALES_2009_4              INDEX PARTITION12 SALES_2009_5               TS_SALES_2009_5              INDEX PARTITION13 SALES_2009_6               TS_SALES_2009_6              INDEX PARTITION14 SALES_2009_7               TS_SALES_2009_7              INDEX PARTITION15 SALES_2009_8               TS_SALES_2009_8              INDEX PARTITION16 17 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE18 ------------------------------ ------------------------------ ------------------19 SALES_2009_9               TS_SALES_2009_9              INDEX PARTITION20 SALES_2010_1               TS_SALES_2010_1              INDEX PARTITION21 22 13 rows selected.

  下面创建一个临时表，并在表上创建索引。

1 SQL> show user;2 USER is "JACK"3 SQL> create table tmp_sale_data_2009_1 as select * from sale_data where 1=2;4 5 Table created.6 7 SQL> create index ind_tmp_sale_data_2009_1 on tmp_sale_data_2009_1(sales_date);8 9 Index created.

  最初的时候，我们看到临时表和临时表的索引都默认存放在USERS表空间上。

SQL> select tablespace_name,table_name from user_tables where table_name ='TMP_SALE_DATA_2009_1';TABLESPACE_NAME            TABLE_NAME------------------------------ ------------------------------USERS                   TMP_SALE_DATA_2009_1------在user_segments中居然没查到-------SQL> select tablespace_name,segment_name,segment_type from user_segments;TABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE------------------------------ ------------------------------ ------------------TS_SALES_2009_8            SALE_DATA              TABLE PARTITIONTS_SALES_2009_9            SALE_DATA              TABLE PARTITIONTS_SALES_2009_10           SALE_DATA              TABLE PARTITIONTS_SALES_2009_11           SALE_DATA              TABLE PARTITIONTS_SALES_2009_12           SALE_DATA              TABLE PARTITIONTS_SALES_2010_1            SALE_DATA              TABLE PARTITIONTS_SALES_2009_1            SALE_DATA              TABLE PARTITIONTS_SALES_2009_2            SALE_DATA              TABLE PARTITIONTS_SALES_2009_3            SALE_DATA              TABLE PARTITIONTS_SALES_2009_4            SALE_DATA              TABLE PARTITIONTS_SALES_2009_5            SALE_DATA              TABLE PARTITIONTABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE------------------------------ ------------------------------ ------------------TS_SALES_2009_6            SALE_DATA              TABLE PARTITIONTS_SALES_2009_7            SALE_DATA              TABLE PARTITIONTS_SALES_2009_1            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_2            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_3            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_4            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_5            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_6            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_7            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_8            IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_9            IND_SALE_DATA              INDEX PARTITIONTABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE------------------------------ ------------------------------ ------------------TS_SALES_2009_10           IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_11           IND_SALE_DATA              INDEX PARTITIONTS_SALES_2009_12           IND_SALE_DATA              INDEX PARTITIONTS_SALES_2010_1            IND_SALE_DATA              INDEX PARTITION26 rows selected.

  首先向要交换的分区中插入一些数据。

 1 SQL> begin  2   2    for i in 1..10000 loop 3   3   insert into sale_data values(i,'alan',i*10,to_date('2009-01-12','yyyy-mm-dd')); 4   4    end loop; 5   5    commit; 6   6  end; 7   7  / 8  9 PL/SQL procedure successfully completed.10 11 SQL> select count(*) from sale_data;12 13   COUNT(*)14 ----------15      1000016 17 SQL> select count(*) from sale_data partition(SALES_2009_1); 18 19   COUNT(*)20 ----------21      10000

  可以看到，要交换的分区SALES_2009_1包含了10000条记录。
  下面开机进行分区交换，包括分区索引，一并交换到临时表上。

1 SQL> alter table sale_data exchange partition sales_2009_12   2    with table tmp_sale_data_2009_1 including indexes with validation;3 4 Table altered.

  完成了分区交换，下面我们来看看各个对象所在的表空间的情况。

 1 SQL> select partition_name,tablespace_name,segment_type from user_segments 2   2    where segment_name ='SALE_DATA' or segment_name = 'IND_SALE_DATA' order by 2; 3  4 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE 5 ------------------------------ ------------------------------ ------------------ 6 SALES_2009_10               TS_SALES_2009_10           TABLE PARTITION 7 SALES_2009_10               TS_SALES_2009_10           INDEX PARTITION 8 SALES_2009_11               TS_SALES_2009_11           INDEX PARTITION 9 SALES_2009_11               TS_SALES_2009_11           TABLE PARTITION10 SALES_2009_12               TS_SALES_2009_12           INDEX PARTITION11 SALES_2009_12               TS_SALES_2009_12           TABLE PARTITION12 SALES_2009_2               TS_SALES_2009_2              INDEX PARTITION13 SALES_2009_2               TS_SALES_2009_2              TABLE PARTITION14 SALES_2009_3               TS_SALES_2009_3              INDEX PARTITION15 SALES_2009_3               TS_SALES_2009_3              TABLE PARTITION16 SALES_2009_4               TS_SALES_2009_4              INDEX PARTITION17 18 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE19 ------------------------------ ------------------------------ ------------------20 SALES_2009_4               TS_SALES_2009_4              TABLE PARTITION21 SALES_2009_5               TS_SALES_2009_5              INDEX PARTITION22 SALES_2009_5               TS_SALES_2009_5              TABLE PARTITION23 SALES_2009_6               TS_SALES_2009_6              TABLE PARTITION24 SALES_2009_6               TS_SALES_2009_6              INDEX PARTITION25 SALES_2009_7               TS_SALES_2009_7              TABLE PARTITION26 SALES_2009_7               TS_SALES_2009_7              INDEX PARTITION27 SALES_2009_8               TS_SALES_2009_8              TABLE PARTITION28 SALES_2009_8               TS_SALES_2009_8              INDEX PARTITION29 SALES_2009_9               TS_SALES_2009_9              INDEX PARTITION30 SALES_2009_9               TS_SALES_2009_9              TABLE PARTITION31 32 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE33 ------------------------------ ------------------------------ ------------------34 SALES_2010_1               TS_SALES_2010_1              INDEX PARTITION35 SALES_2010_1               TS_SALES_2010_1              TABLE PARTITION36 SALES_2009_1               USERS                  INDEX PARTITION37 SALES_2009_1               USERS                  TABLE PARTITION38 39 26 rows selected.40 41 SQL> select tablespace_name,segment_name,segment_type from user_segments42   2     where segment_name like '%SALE_DATA_2009_1';43 44 TABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE45 ------------------------------ ------------------------------ ------------------46 TS_SALES_2009_1            TMP_SALE_DATA_2009_1          TABLE47 TS_SALES_2009_1            IND_TMP_SALE_DATA_2009_1       INDEX

  我们看到，分区SALES_2009_1和相应的分区索引，已经交换到了USERS表空间上；而临时表TMP_SALE_DATA_2009_1和它的索引IND_TMP_SALE_DATA_2009_1交换到了以前分区所在的表空间TS_SALES_2009_1上。

  现在分区中的数据已经交换到了临时表中。

 1 SQL> select count(*) from sale_data partition(SALES_2009_1); 2  3   COUNT(*) 4 ---------- 5      0 6  7 SQL> select count(*) from sale_data; 8  9   COUNT(*)10 ----------11      012 13 SQL> select count(*) from tmp_sale_data_2009_1;14 15   COUNT(*)16 ----------17      10000

这样就完成了表空间的交换，表空间TS_SALES_2009_1上面的对象就是只有一个自包含的表TMP_SALE_DATA_2009_1。

可以通过下面的操作来验证它。

 1 SQL> conn /as sysdba 2 Connected. 3  4 SQL> exec dbms_tts.transport_set_check('TS_SALES_2009_1',TRUE); 5  6 PL/SQL procedure successfully completed. 7  8 SQL> select * from transport_set_violations; 9 10 no rows selected

我们看到，transport_set_violations表中没有数据，说明空间已经通过了检查，可以进行表空间传递操作了。
在进行表空间传递导出前，需要将要操作的表空间设置为只读。

1 SQL> alter tablespace TS_SALES_2009_1 read only;2 3 Tablespace altered.

下面正式开始导出表空间TS_SALES_2009_1。

 1 SQL> conn /as sysdba 2 Connected. 3 SQL> create directory exp_trans_ts as '/home/oracle/temp'; 4  5 Directory created. 6  7 [oracle@node2 temp]$ expdp system/oracle DIRECTORY=exp_trans_ts DUMPFILE=tts.dmp TRANSPORT_TABLESPACES= TS_SALES_2009_1 TRANSPORT_FULL_CHECK=y 8  9 Export: Release 11.2.0.1.0 - Production on Wed Dec 19 15:47:30 201210 11 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.12 13 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production14 With the Partitioning, OLAP, Data Mining and Real Application Testing options15 Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DIRECTORY=exp_trans_ts DUMPFILE=tts.dmp TRANSPORT_TABLESPACES= TS_SALES_2009_1 TRANSPORT_FULL_CHECK=y 16 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK17 Processing object type TRANSPORTABLE_EXPORT/TABLE18 Processing object type TRANSPORTABLE_EXPORT/INDEX19 Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS20 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK21 Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded22 ******************************************************************************23 Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:24   /home/oracle/temp/tts.dmp25 ******************************************************************************26 Datafiles required for transportable tablespace TS_SALES_2009_1:27   /u01/app/oracle/oradata/YFT1/YFT1/datafile/o1_mf_ts_sales_8f2ot797_.dbf28 Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:48:56

  关于expdp和impdp的使用请查看： 《Expdp and Impdp》

  导出完成后，接下来的工作就是将导出的文件复制到离线数据库服务器上(YFT2)。

五、数据导入到YFT2

  在离线数据库中，首先要创建好需要导入的分区表，直接创建在默认的表空间上就可以，因为这些分区都是空的。

 1 [oracle@node2 ~]$ env |grep ORA 2 ORACLE_SID=YFT1 3 ORACLE_BASE=/u01/app/oracle 4 ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 5 [oracle@node2 ~]$ sqlplus /nolog 6  7 SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 19 13:56:53 2012 8  9 Copyright (c) 1982, 2009, Oracle.  All rights reserved.10 11 SQL> conn /as sysdba12 Connected.13 14 SQL> create user jack identified by jack;15 16 User created.17 18 SQL> grant dba to jack;19 20 Grant succeeded.21 22 SQL> conn jack/jack23 Connected.24 25 SQL> create table sale_data (sale_id number(5),salesmane_name varchar2(30),sales_amount number(10),sales_date date)26   2     partition by range(sales_date)27   3  (28   4     partition sales_2009_1 values less than(to_date('01/02/2009','DD/MM/YYYY')) , 29   5     partition sales_2009_2 values less than(to_date('01/03/2009','DD/MM/YYYY')) ,      30   6     partition sales_2009_3 values less than(to_date('01/04/2009','DD/MM/YYYY')) ,  31   7     partition sales_2009_4 values less than(to_date('01/05/2009','DD/MM/YYYY')) ,32   8     partition sales_2009_5 values less than(to_date('01/06/2009','DD/MM/YYYY')) ,33   9     partition sales_2009_6 values less than(to_date('01/07/2009','DD/MM/YYYY')) , 34  10     partition sales_2009_7 values less than(to_date('01/08/2009','DD/MM/YYYY')) ,35  11     partition sales_2009_8 values less than(to_date('01/09/2009','DD/MM/YYYY')) , 36  12     partition sales_2009_9 values less than(to_date('01/10/2009','DD/MM/YYYY')) ,  37  13     partition sales_2009_10 values less than(to_date('01/11/2009','DD/MM/YYYY')) , 38  14     partition sales_2009_11 values less than(to_date('01/12/2009','DD/MM/YYYY')) , 39  15     partition sales_2009_12 values less than(to_date('01/01/2010','DD/MM/YYYY')), 40  16     partition sales_2010_1 values less than(to_date('01/02/2010','DD/MM/YYYY')) 41  17  );42 43 Table created.44 45 SQL> create index ind_sale_data on sale_data(sales_date)46   2    local47   3  (48   4    partition sales_2009_1 tablespace users,49   5    partition sales_2009_2 tablespace users,50   6    partition sales_2009_3 tablespace users,51   7    partition sales_2009_4 tablespace users,52   8    partition sales_2009_5 tablespace users,53   9    partition sales_2009_6 tablespace users, 54  10    partition sales_2009_7 tablespace users,55  11    partition sales_2009_8 tablespace users,56  12    partition sales_2009_9 tablespace users,57  13    partition sales_2009_10 tablespace users,58  14    partition sales_2009_11 tablespace users,59  15    partition sales_2009_12 tablespace users,60  16    partition sales_2010_1 tablespace users61  17  );62 63 Index created.

 1 ---------首先把数据文件拷贝过来----------- 2 [oracle@node2 datafile]$ cp o1_mf_ts_sales_8f2ot797_.dbf /u01/app/oracle/oradata/YFT2/YFT2/datafile/ 3  4 --------开始导入---------------- 5 SQL> create directory exp_trans_ts as '/home/oracle/temp'; 6  7 Directory created. 8  9 [oracle@node2 datafile]$ impdp system/oracle DIRECTORY=exp_trans_ts dumpfile=tts.dmp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/YFT2/YFT2/datafile/o1_mf_ts_sales_8f2ot797_.dbf'10 11 Import: Release 11.2.0.1.0 - Production on Wed Dec 19 16:11:55 201212 13 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.14 15 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production16 With the Partitioning, OLAP, Data Mining and Real Application Testing options17 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded18 Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** DIRECTORY=exp_trans_ts dumpfile=tts.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/YFT2/YFT2/datafile/o1_mf_ts_sales_8f2ot797_.dbf 19 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK20 Processing object type TRANSPORTABLE_EXPORT/TABLE21 Processing object type TRANSPORTABLE_EXPORT/INDEX22 Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS23 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK24 Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:11:59

 

  这时候我们看到，临时表和表空间以及表中的数据都导入到了离线数据库中。

1 SQL> col segment_name for a30;2 SQL> select tablespace_name,segment_name,segment_type from user_segments3   2     where segment_name like '%SALE_DATA_2009_1';4 5 TABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE6 ------------------------------ ------------------------------ ------------------7 TS_SALES_2009_1            TMP_SALE_DATA_2009_1          TABLE8 TS_SALES_2009_1            IND_TMP_SALE_DATA_2009_1       INDEX

 此时离线数据库中的分区及索引所在的表空间情况如下：

 1 SQL> select partition_name,tablespace_name,segment_type from user_segments 2   2    where segment_name = 'SALE_DATA' or segment_name='IND_SALE_DATA' order by 2; 3  4 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE 5 ------------------------------ ------------------------------ ------------------ 6 SALES_2009_1               USERS                  INDEX PARTITION 7 SALES_2010_1               USERS                  TABLE PARTITION 8 SALES_2009_11               USERS                  INDEX PARTITION 9 SALES_2009_12               USERS                  INDEX PARTITION10 SALES_2009_2               USERS                  INDEX PARTITION11 SALES_2009_3               USERS                  INDEX PARTITION12 SALES_2009_4               USERS                  INDEX PARTITION13 SALES_2009_5               USERS                  INDEX PARTITION14 SALES_2009_6               USERS                  INDEX PARTITION15 SALES_2009_7               USERS                  INDEX PARTITION16 SALES_2009_8               USERS                  INDEX PARTITION17 18 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE19 ------------------------------ ------------------------------ ------------------20 SALES_2009_9               USERS                  INDEX PARTITION21 SALES_2010_1               USERS                  INDEX PARTITION22 SALES_2009_1               USERS                  TABLE PARTITION23 SALES_2009_10               USERS                  TABLE PARTITION24 SALES_2009_11               USERS                  TABLE PARTITION25 SALES_2009_12               USERS                  TABLE PARTITION26 SALES_2009_2               USERS                  TABLE PARTITION27 SALES_2009_3               USERS                  TABLE PARTITION28 SALES_2009_4               USERS                  TABLE PARTITION29 SALES_2009_5               USERS                  TABLE PARTITION30 SALES_2009_6               USERS                  TABLE PARTITION31 32 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE33 ------------------------------ ------------------------------ ------------------34 SALES_2009_7               USERS                  TABLE PARTITION35 SALES_2009_8               USERS                  TABLE PARTITION36 SALES_2009_9               USERS                  TABLE PARTITION37 SALES_2009_10               USERS                  INDEX PARTITION38 39 26 rows selected.

  表中的数据情况：

 1 SQL> select count(*) from tmp_sale_data_2009_1; 2  3   COUNT(*) 4 ---------- 5      10000 6  7 SQL> select count(*) from sale_data partition(SALES_2009_1); 8  9   COUNT(*)10 ----------11      0

  最后一步的工作就是将导入的临时表交换到离线数据库中的分区表中。

 1 SQL> alter table sale_data exchange partition sales_2009_1 2   2     with table tmp_sale_data_2009_1 including indexes with validation; 3  4 Table altered. 5  6 SQL> select tablespace_name,segment_name,segment_type from user_segments 7   2     where segment_name like '%SALE_DATA_2009_1'; 8  9 TABLESPACE_NAME            SEGMENT_NAME              SEGMENT_TYPE10 ------------------------------ ------------------------------ ------------------11 USERS                   TMP_SALE_DATA_2009_1          TABLE12 USERS                   IND_TMP_SALE_DATA_2009_1       INDEX13 14 SQL> select partition_name,tablespace_name,segment_type from user_segments15   2     where segment_name = 'SALE_DATA' or segment_name='IND_SALE_DATA' order by 2;16 17 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE18 ------------------------------ ------------------------------ ------------------19 SALES_2009_1               TS_SALES_2009_1              INDEX PARTITION20 SALES_2009_1               TS_SALES_2009_1              TABLE PARTITION21 SALES_2009_12               USERS                  INDEX PARTITION22 SALES_2009_2               USERS                  INDEX PARTITION23 SALES_2009_3               USERS                  INDEX PARTITION24 SALES_2009_4               USERS                  INDEX PARTITION25 SALES_2009_5               USERS                  INDEX PARTITION26 SALES_2009_6               USERS                  INDEX PARTITION27 SALES_2009_7               USERS                  INDEX PARTITION28 SALES_2009_8               USERS                  INDEX PARTITION29 SALES_2009_9               USERS                  INDEX PARTITION30 31 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE32 ------------------------------ ------------------------------ ------------------33 SALES_2010_1               USERS                  INDEX PARTITION34 SALES_2009_10               USERS                  TABLE PARTITION35 SALES_2009_11               USERS                  TABLE PARTITION36 SALES_2009_12               USERS                  TABLE PARTITION37 SALES_2009_2               USERS                  TABLE PARTITION38 SALES_2009_3               USERS                  TABLE PARTITION39 SALES_2009_4               USERS                  TABLE PARTITION40 SALES_2009_5               USERS                  TABLE PARTITION41 SALES_2009_6               USERS                  TABLE PARTITION42 SALES_2009_7               USERS                  TABLE PARTITION43 SALES_2009_8               USERS                  TABLE PARTITION44 45 PARTITION_NAME               TABLESPACE_NAME              SEGMENT_TYPE46 ------------------------------ ------------------------------ ------------------47 SALES_2009_9               USERS                  TABLE PARTITION48 SALES_2009_11               USERS                  INDEX PARTITION49 SALES_2010_1               USERS                  TABLE PARTITION50 SALES_2009_10               USERS                  INDEX PARTITION51 52 26 rows selected.

  我们看到，传递过来的表空间TS_SALES_2009_1上的数据和索引已经交换到了离线数据库的分区SALES_2009_1上。

SQL> select count(*) from tmp_sale_data_2009_1;  COUNT(*)----------     0SQL> select count(*) from sale_data partition(SALES_2009_1);  COUNT(*)----------     10000

  临时表中已经没有了数据，而以前空的分区现在有了10000条从在线数据库中传递过来的数据。

六、总结

  1、在在线数据库中的通过临时表的方式将需要过期处理的分区数据和索引交换出来；

  2、通过表空间传递的方式导出表空间；

  3、将导出的dmp文件和表空间数据文件拷贝到离线数据库中；

  4、在离线数据库中导入表空间；

  5、将导入表空间上的临时表数据和索引交换到离线数据库的分区表中。

  对于离线数据库，通常数据是不做修改的，可以将表空间设置为只读，有利于数据的安全；同时也可以考虑进行分区压缩，提高数据处理的速度。