特别说明：该案例引自谭老师的《让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 sysdba 12 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_1 28 17 ); 29 30 Table created. 31 32 SQL> create index ind_sale_data on sale_data(sales_date) 33 2 local 34 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_1 48 17 ); 49 50 Index created. 51 52 SQL> select partition_name,tablespace_name from user_segments 53 2 where segment_name in ('SALE_DATA','IND_SALE_DATA'); 54 55 PARTITION_NAME TABLESPACE_NAME 56 ------------------------------ ------------------------------ 57 SALES_2009_1 TS_SALES_2009_1 58 SALES_2009_10 TS_SALES_2009_10 59 SALES_2009_11 TS_SALES_2009_11 60 SALES_2009_12 TS_SALES_2009_12 61 SALES_2009_2 TS_SALES_2009_2 62 SALES_2009_3 TS_SALES_2009_3 63 SALES_2009_4 TS_SALES_2009_4 64 SALES_2009_5 TS_SALES_2009_5 65 SALES_2009_6 TS_SALES_2009_6 66 SALES_2009_7 TS_SALES_2009_7 67 SALES_2009_8 TS_SALES_2009_8 68 69 PARTITION_NAME TABLESPACE_NAME 70 ------------------------------ ------------------------------ 71 SALES_2009_9 TS_SALES_2009_9 72 SALES_2010_1 TS_SALES_2010_1 73 SALES_2009_1 TS_SALES_2009_1 74 SALES_2009_10 TS_SALES_2009_10 75 SALES_2009_11 TS_SALES_2009_11 76 SALES_2009_12 TS_SALES_2009_12 77 SALES_2009_2 TS_SALES_2009_2 78 SALES_2009_3 TS_SALES_2009_3 79 SALES_2009_4 TS_SALES_2009_4 80 SALES_2009_5 TS_SALES_2009_5 81 SALES_2009_6 TS_SALES_2009_6 82 83 PARTITION_NAME TABLESPACE_NAME 84 ------------------------------ ------------------------------ 85 SALES_2009_7 TS_SALES_2009_7 86 SALES_2009_8 TS_SALES_2009_8 87 SALES_2009_9 TS_SALES_2009_9 88 SALES_2010_1 TS_SALES_2010_1 89 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 VIOLATIONS 10 -------------------------------------------------------------------------------- 11 ORA-39921: Default Partition (Table) Tablespace USERS for SALE_DATA not containe 12 d in transportable set. 13 14 ORA-39901: Partitioned table JACK.SALE_DATA is partially contained in the transp 15 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 PARTITION 10 SALES_2009_2 TS_SALES_2009_2 TABLE PARTITION 11 SALES_2009_3 TS_SALES_2009_3 TABLE PARTITION 12 SALES_2009_4 TS_SALES_2009_4 TABLE PARTITION 13 SALES_2009_5 TS_SALES_2009_5 TABLE PARTITION 14 SALES_2009_6 TS_SALES_2009_6 TABLE PARTITION 15 SALES_2009_7 TS_SALES_2009_7 TABLE PARTITION 16 SALES_2009_8 TS_SALES_2009_8 TABLE PARTITION 17 18 PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE 19 ------------------------------ ------------------------------ ------------------ 20 SALES_2009_9 TS_SALES_2009_9 TABLE PARTITION 21 SALES_2010_1 TS_SALES_2010_1 TABLE PARTITION 22 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 PARTITION 10 SALES_2009_3 TS_SALES_2009_3 INDEX PARTITION 11 SALES_2009_4 TS_SALES_2009_4 INDEX PARTITION 12 SALES_2009_5 TS_SALES_2009_5 INDEX PARTITION 13 SALES_2009_6 TS_SALES_2009_6 INDEX PARTITION 14 SALES_2009_7 TS_SALES_2009_7 INDEX PARTITION 15 SALES_2009_8 TS_SALES_2009_8 INDEX PARTITION 16 17 PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE 18 ------------------------------ ------------------------------ ------------------ 19 SALES_2009_9 TS_SALES_2009_9 INDEX PARTITION 20 SALES_2010_1 TS_SALES_2010_1 INDEX PARTITION 21 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 PARTITION TS_SALES_2009_9 SALE_DATA TABLE PARTITION TS_SALES_2009_10 SALE_DATA TABLE PARTITION TS_SALES_2009_11 SALE_DATA TABLE PARTITION TS_SALES_2009_12 SALE_DATA TABLE PARTITION TS_SALES_2010_1 SALE_DATA TABLE PARTITION TS_SALES_2009_1 SALE_DATA TABLE PARTITION TS_SALES_2009_2 SALE_DATA TABLE PARTITION TS_SALES_2009_3 SALE_DATA TABLE PARTITION TS_SALES_2009_4 SALE_DATA TABLE PARTITION TS_SALES_2009_5 SALE_DATA TABLE PARTITION TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------------------ ------------------ TS_SALES_2009_6 SALE_DATA TABLE PARTITION TS_SALES_2009_7 SALE_DATA TABLE PARTITION TS_SALES_2009_1 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_2 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_3 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_4 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_5 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_6 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_7 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_8 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_9 IND_SALE_DATA INDEX PARTITION TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE ------------------------------ ------------------------------ ------------------ TS_SALES_2009_10 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_11 IND_SALE_DATA INDEX PARTITION TS_SALES_2009_12 IND_SALE_DATA INDEX PARTITION TS_SALES_2010_1 IND_SALE_DATA INDEX PARTITION 26 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 10000 16 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_1 2 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 PARTITION 10 SALES_2009_12 TS_SALES_2009_12 INDEX PARTITION 11 SALES_2009_12 TS_SALES_2009_12 TABLE PARTITION 12 SALES_2009_2 TS_SALES_2009_2 INDEX PARTITION 13 SALES_2009_2 TS_SALES_2009_2 TABLE PARTITION 14 SALES_2009_3 TS_SALES_2009_3 INDEX PARTITION 15 SALES_2009_3 TS_SALES_2009_3 TABLE PARTITION 16 SALES_2009_4 TS_SALES_2009_4 INDEX PARTITION 17 18 PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE 19 ------------------------------ ------------------------------ ------------------ 20 SALES_2009_4 TS_SALES_2009_4 TABLE PARTITION 21 SALES_2009_5 TS_SALES_2009_5 INDEX PARTITION 22 SALES_2009_5 TS_SALES_2009_5 TABLE PARTITION 23 SALES_2009_6 TS_SALES_2009_6 TABLE PARTITION 24 SALES_2009_6 TS_SALES_2009_6 INDEX PARTITION 25 SALES_2009_7 TS_SALES_2009_7 TABLE PARTITION 26 SALES_2009_7 TS_SALES_2009_7 INDEX PARTITION 27 SALES_2009_8 TS_SALES_2009_8 TABLE PARTITION 28 SALES_2009_8 TS_SALES_2009_8 INDEX PARTITION 29 SALES_2009_9 TS_SALES_2009_9 INDEX PARTITION 30 SALES_2009_9 TS_SALES_2009_9 TABLE PARTITION 31 32 PARTITION_NAME TABLESPACE_NAME SEGMENT_TYPE 33 ------------------------------ ------------------------------ ------------------ 34 SALES_2010_1 TS_SALES_2010_1 INDEX PARTITION 35 SALES_2010_1 TS_SALES_2010_1 TABLE PARTITION 36 SALES_2009_1 USERS INDEX PARTITION 37 SALES_2009_1 USERS TABLE PARTITION 38 39 26 rows selected. 40 41 SQL> select tablespace_name,segment_name,segment_type from user_segments 42 2 where segment_name like '%SALE_DATA_2009_1'; 43 44 TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE 45 ------------------------------ ------------------------------ ------------------ 46 TS_SALES_2009_1 TMP_SALE_DATA_2009_1 TABLE 47 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