使用技术：分区交换和传输表空间
一、准备数据
1、为每个分区创建独立表空间
alter system set db_create_file_dest='/u01/app/oracle/oradata/eisoo/';
create tablespace tbs_sales_2016_1 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_2 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_3 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_4 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_5 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_6 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_7 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_8 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_9 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_10 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_11 datafile size 50M autoextend on;
create tablespace tbs_sales_2016_12 datafile size 50M autoextend on;
create tablespace tbs_sales_2017_1 datafile size 50M autoextend on;
2、创建分区表
create table sale_data
(
sale_id number(5),
salesman_name varchar2(30),
sales_amount number(10),
sales_date date
)
partition by range(sales_date)
(
partition sales_2016_1 values less than (to_date('01/02/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_1,
partition sales_2016_2 values less than (to_date('01/03/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_2,
partition sales_2016_3 values less than (to_date('01/04/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_3,
partition sales_2016_4 values less than (to_date('01/05/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_4,
partition sales_2016_5 values less than (to_date('01/06/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_5,
partition sales_2016_6 values less than (to_date('01/07/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_6,
partition sales_2016_7 values less than (to_date('01/08/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_7,
partition sales_2016_8 values less than (to_date('01/09/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_8,
partition sales_2016_9 values less than (to_date('01/10/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_9,
partition sales_2016_10 values less than (to_date('01/11/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_10,
partition sales_2016_11 values less than (to_date('01/12/2016','DD/MM/YYYY')) tablespace tbs_sales_2016_11,
partition sales_2016_12 values less than (to_date('01/01/2017','DD/MM/YYYY')) tablespace tbs_sales_2016_12,
partition sales_2017_1 values less than (to_date('01/02/2017','DD/MM/YYYY')) tablespace tbs_sales_2017_1
);
3、创建索引
create index ind_sale_data on sale_data(sales_date)
local
(
partition sales_2016_1 tablespace tbs_sales_2016_1,
partition sales_2016_2 tablespace tbs_sales_2016_2,
partition sales_2016_3 tablespace tbs_sales_2016_3,
partition sales_2016_4 tablespace tbs_sales_2016_4,
partition sales_2016_5 tablespace tbs_sales_2016_5,
partition sales_2016_6 tablespace tbs_sales_2016_6,
partition sales_2016_7 tablespace tbs_sales_2016_7,
partition sales_2016_8 tablespace tbs_sales_2016_8,
partition sales_2016_9 tablespace tbs_sales_2016_9,
partition sales_2016_10 tablespace tbs_sales_2016_10,
partition sales_2016_11 tablespace tbs_sales_2016_11,
partition sales_2016_12 tablespace tbs_sales_2016_12,
partition sales_2017_1 tablespace tbs_sales_2017_1
);
查看上面创建的分区表和索引
select partition_name,tablespace_name from user_segments where segment_name in ('SALE_DATA','IND_SALE_DATA');
4、验证分区表空间TBS_SALES_2016_1是否满足传输表空间的条件
exec dbms_tts.transport_set_check('TBS_SALES_2016_1',TRUE);
select * from transport_set_violations;
5、由于表空间TBS_SALES_2016_1不满足传输表空间的条件，所以可是通过分区交换之后，再使用传输表空间技术
（1）创建临时表和索引
create table tmp_sale_data_2016_1 as select * from sale_data where 1=2;
create index ind_tmp_sale_data_2016_1 on tmp_sale_data_2016_1(SALES_DATE);
（2）查看临时表信息
SQL> select tablespace_name,segment_name,segment_type from user_segments where segment_name like '%SALE_DATA_2016_1';
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
TESTTBS IND_TMP_SALE_DATA_2016_1 INDEX
TESTTBS TMP_SALE_DATA_2016_1 TABLE
（3）向要交换的分区中插入数据，已验证交换结果
begin
for i in 1..10000 loop
insert into sale_data values(i,'tom',i*10,to_date('2016-01-15','yyyy-mm-dd'));
end loop;
commit;
end;
/
（4）开始分区交换
alter table sale_data exchange partition sales_2016_1 with table tmp_sale_data_2016_1 including indexes with validation;
（5）验证结果
select count(*) from sale_data partition(sales_2016_1);
select count(*) from tmp_sale_data_2016_1;
二、下面使用传输表空间技术，将表空间从生产库导入到离线数据库
1、首先在生产库中将要传输的表空间置为read only
alter tablespace tbs_sales_2016_1 read only;
2、导出数据
exp \'sys/oracle as sysdba\' tablespaces=tbs_sales_2016_1 transport_tablespace=y file=/home/oracle/expbk/tbs_sales_2016_1.dmp log=/home/oracle/expbk/tbs_sales_2016_1.log
3、将生产库中要传输的分区表空间的数据文件/u01/app/oracle/oradata/eisoo/EISOO/datafile/o1_mf_tbs_sale_ch9tf97w_.dbf以及上面导出的文件/home/oracle/expbk/tbs_sales_2016_1.dmp拷贝到离线数据库（oracle用户有权限操作的目录下）
4、在离线数据库中创建和生产库中相同的用户（分区表的拥有者），并在该用户下创建对应的分区表（空表）以及索引
create user fst identified by fst;
grant connect,resource,dba to fst;
create table sale_data
(
sale_id number(5),
salesman_name varchar2(30),
sales_amount number(10),
sales_date date
)
partition by range(sales_date)
(
partition sales_2016_1 values less than (to_date('01/02/2016','DD/MM/YYYY')),
partition sales_2016_2 values less than (to_date('01/03/2016','DD/MM/YYYY')),
partition sales_2016_3 values less than (to_date('01/04/2016','DD/MM/YYYY')),
partition sales_2016_4 values less than (to_date('01/05/2016','DD/MM/YYYY')),
partition sales_2016_5 values less than (to_date('01/06/2016','DD/MM/YYYY')),
partition sales_2016_6 values less than (to_date('01/07/2016','DD/MM/YYYY')),
partition sales_2016_7 values less than (to_date('01/08/2016','DD/MM/YYYY')),
partition sales_2016_8 values less than (to_date('01/09/2016','DD/MM/YYYY')),
partition sales_2016_9 values less than (to_date('01/10/2016','DD/MM/YYYY')),
partition sales_2016_10 values less than (to_date('01/11/2016','DD/MM/YYYY')),
partition sales_2016_11 values less than (to_date('01/12/2016','DD/MM/YYYY')),
partition sales_2016_12 values less than (to_date('01/01/2017','DD/MM/YYYY')),
partition sales_2017_1 values less than (to_date('01/02/2017','DD/MM/YYYY'))
);
create index ind_sale_data on sale_data(sales_date)
local
(
partition sales_2016_1,
partition sales_2016_2,
partition sales_2016_3,
partition sales_2016_4,
partition sales_2016_5,
partition sales_2016_6,
partition sales_2016_7,
partition sales_2016_8,
partition sales_2016_9,
partition sales_2016_10,
partition sales_2016_11,
partition sales_2016_12,
partition sales_2017_1
);
5、在离线数据库中导入临时表以及表空间
imp \'sys/oracle as sysdba\' tablespaces=tbs_sales_2016_1 transport_tablespace=y file=/home/oracle/tbs_sales_2016_1.dmp datafiles=/u01/app/oracle/oradata/db01/o1_mf_tbs_sale_ch9tf97w_.dbf log=/home/oracle/imptbs01.log
6、验证导入情况（fst用户下）
SQL> select tablespace_name,segment_name,segment_type from user_segments where segment_name like '%SALE_DATA_2016_1';
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
TBS_SALES_2016_1 TMP_SALE_DATA_2016_1 TABLE
TBS_SALES_2016_1 IND_TMP_SALE_DATA_2016_1 INDEX
7、将导入的临时表和刚才创建的分区表交换空间
alter table sale_data exchange partition sales_2016_1 with table TMP_SALE_DATA_2016_1 including indexes with validation;
验证
select partition_name,tablespace_name,segment_type from user_segments where segment_name='SALE_DATA' or segment_name='IND_SALE_DATA' order by 2;
SQL> select count(*) from sale_data partition(sales_2016_1);
COUNT(*)
----------
10000
至此生产库中表sale_data的sales_2016_1分区数据成功迁移到离线数据中，在生产库中可将该分区以及它所使用的表空间删除以释放生产库空间。