传输表空间在分区表上的应用条件如下
1.分区放在不同的表空间上
2.建立与分区表结构（字段、索引）一样的非分区普通表
3.通过使用exchange partition功能将表分区与非分区表交换
4.使用TTS将数据迁移到另一台DB上
具体操作方法如下

1.分区放在不同的表空间上，分区表AIR_ORDERS按天分区，一个季度有一个数据表空间和一个索引表空间，
可以使用以下plsql块将两个季度的数据交换到非分区表.
sql> begin
  2 for n in (select *
  3 from dba_segments
  4 where segment_name = 'AIR_ORDERS'
  5    and (partition_name like 'AIR_ORDERS_201007%' or
  6       partition_name like 'AIR_ORDERS_201008%' or
  7       partition_name like 'AIR_ORDERS_201009%' or
  8       partition_name like 'AIR_ORDERS_201010%'or
  9       partition_name like 'AIR_ORDERS_201011%'or
10       partition_name like 'AIR_ORDERS_201012%')) loop
11    execute immediate 'create table ' || n.partition_name ||'_ex as select * from AIR_ORDERS where 1=0';
12    execute immediate 'CREATE INDEX IND_AO_TIME' ||substr(n.partition_name,10,length(n.partition_name)) || ' ON ' ||n.partition_name || '_ex(TIME)';
13    execute immediate 'CREATE INDEX IND_AO_CN' ||substr(n.partition_name,10,length(n.partition_name)) || ' ON ' ||n.partition_name || '_ex(CN)';
14    execute immediate 'alter table AIR_ORDERS exchange partition '||n.partition_name||' with table '||n.partition_name || '_ex including indexes';
15 end loop;
16  end;
17  /

PL/SQL procedure successfully completed.

2.检查表空间的依赖
sql> exec dbms_tts.transport_set_check('AO_DATA_1007,AO_DATA_1010,AO_INDEX_1010,AO_INDEX_1007', TRUE,TRUE);

PL/SQL procedure successfully completed.

sql> SELECT * FROM transport_set_violations;

no rows selected
以上检查全部通过

3.将表空间设置为read only
sql> alter tablespace AO_DATA_1007 read only;

Tablespace altered.

sql> alter tablespace AO_DATA_1010 read only;

Tablespace altered.

sql> alter tablespace AO_INDEX_1010 read only;

Tablespace altered.

sql> alter tablespace AO_INDEX_1007 read only;

Tablespace altered.

4.导出表传输表空间定义信息
db1$expdp \'/ as sysdba\' directory=DATA_PUMP_DIR  dumpfile=AIR_ORDERS_ex.dmp logfile=AIR_ORDERS_ex.log SCHEMAS=air
TRANSPORT_TABLESPACES=$AO_DATA_1007,AO_DATA_1010,AO_INDEX_1010,AO_INDEX_1007$;

5.将定义信息与传输表空间数据文件拷贝到目标机db2上
db1$ scp AO_* db2:/U01/app/oracle/oradata/air/
AO_data_1007_01.dbf                                                                               100% 32GB  30.4MB/s 17:57
AO_data_1007_02.dbf                                                                               100% 32GB  28.0MB/s 19:31
.....
db1$ scp AIR_ORDERS_ex.dmp db2:/U02/app/oracle/exp
AIR_ORDERS_ex.dmp                                                                                     100% 4704KB 4.6MB/s 00:01

6.设置传输表空间为读写状态
sql> alter tablespace AO_DATA_1007 read write;

Tablespace altered.

sql> alter tablespace AO_DATA_1010 read write;

Tablespace altered.

sql> alter tablespace AO_INDEX_1010 read write;

Tablespace altered.

sql> alter tablespace AO_INDEX_1007 read write;

7.在目标机db2上导入传输表空间定义信息
db2$impdp \'/ as sysdba\' directory=DATA_PUMP_EXP dumpfile=AIR_ORDERS_ex.dmp logfile=AIR_ORDERS_ex.log REMAP_SCHEMA=air:air_arc
TRANSPORT_DATAFILES=$'/U01/app/oracle/oradata/air/AO_data_1007_01.dbf','/U01/app/oracle/oradata/air/AO_data_1007_02.dbf',
'/U01/app/oracle/oradata/air/AO_data_1007_03.dbf','/U01/app/oracle/oradata/air/AO_data_1010_01.dbf',
'/U01/app/oracle/oradata/air/AO_data_1010_02.dbf','/U01/app/oracle/oradata/air/AO_DATA_1010_03.dbf',
'/U01/app/oracle/oradata/air/AO_index_1007_01.dbf','/U01/app/oracle/oradata/air/AO_INDEX_1007_02.dbf',
'/U01/app/oracle/oradata/air/AO_index_1010_01.dbf','/U01/app/oracle/oradata/air/AO_INDEX_1010_02.dbf',
'/U01/app/oracle/oradata/air/AO_INDEX_1010_03.dbf'$;

Import: Release 10.2.0.4.0 - 64bit Production on Friday, 27 April, 2012 18:31:37

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  '/******** AS SYSDBA' directory=DATA_PUMP_EXP dumpfile=AIR_ORDERS_ex.dmp
logfile=AIR_ORDERS_ex.log  REMAP_SCHEMA=air:air_arc
TRANSPORT_DATAFILES=(/U01/app/oracle/oradata/air/AO_data_1007_01.dbf,/U01/app/oracle/oradata/air/AO_data_1007_02.dbf,
/U01/app/oracle/oradata/air/AO_data_1007_03.dbf,/U01/app/oracle/oradata/air/AO_data_1010_01.dbf,
/U01/app/oracle/oradata/air/AO_data_1010_02.dbf,/U01/app/oracle/oradata/air/AO_DATA_1010_03.dbf,
/U01/app/oracle/oradata/air/AO_index_1007_01.dbf,/U01/app/oracle/oradata/air/AO_INDEX_1007_02.dbf,
/U01/app/oracle/oradata/air/AO_index_1010_01.dbf,/U01/app/oracle/oradata/air/AO_INDEX_1010_02.dbf,
/U01/app/oracle/oradata/air/AO_INDEX_1010_03.dbf)
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:32:45

8.在目标机上将表空间设置为读写状态
SQL> alter tablespace AO_DATA_1007 read write;

Tablespace altered.

SQL> alter tablespace AO_DATA_1010 read write;

Tablespace altered.

SQL> alter tablespace AO_INDEX_1010 read write;

Tablespace altered.

SQL> alter tablespace AO_INDEX_1007 read write;

Tablespace altered.

9.如果目标机上也是分区表，可以利用同样的方法，再交换到分区表中
SQL> begin
  2 for n in (select *
  3             from dba_segments
  4             where tablespace_name in
  5                   ('AO_DATA_1007',
  6                   'AO_DATA_1010',
  7                   'AO_INDEX_1010',
  8                   'AO_INDEX_1007')
  9                and segment_type = 'TABLE') loop
10    execute immediate 'alter table air_arc.AIR_ORDERS exchange partition AIR_ORDERS_' ||
11                         replace(substr(n.segment_name, 10, 20),'_EX','') ||
12                         ' with table air_arc.' || n.segment_name ||
13                         ' including indexes without validation';
14 end loop;
15  end;
16  /

PL/SQL procedure successfully completed.

10.最后可以在db1清理中间表与迁移的表空间数据减少空间占用

ITPub博客

传输表空间在分区表上的应用

最新文章