3、生成可传输的表空间集合

3.1 设置表空间为read only

altertablespace IOTEXTBS read only ;

3.2 exportmetadata

mkdir -p/u01/apps/oracle/tts_dir

create directory tts_dir as'/u01/apps/oracle/tts_dir' ;

expdp system/xiaojundumpfile=tts_iotextbs.dmp directory=tts_dir transport_tablespaces=iotextbslogfile=exp_tts_iotextbs.log

3.3(这里是同平台，所以不必转换，下面给出的是示例语句)

rman convert

CONVERT TABLESPACEsales_1,sales_2
TO PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT '/tmp/%U';

4、传输数据文件以及expdp导出的meta文件到目标数据库服务器上

5、将源端的表空间置于read write模式

altertablespace IOTEXTBS read write ;

6、将元数据导入到目标数据库中

impdpsystem/xiaojundumpfile=tts_iotextbs.dmp directory=tts_dir transport_datafiles=/u01/oinsdir/ottbs01.dbf remap_schema=test:dexter logfile=tts_import.log

关于自包含的问题

以下是测试过程

sys@GG1> create tablespace dextbs datafile'/u01/apps/oracle/oradata/gg1/dextbs01.dbf' size 10m autoextend on next 100m ;

Tablespace created.

sys@GG1> create tablespace dextertbs datafile'/u01/apps/oracle/oradata/gg1/dextertbs01.dbf' size 10m autoextend on next 100m;

Tablespace created.

create table test01 tablespace dextbs as selectlevel id , level || 'name' as name from dual connect by level <= 10000;

create table test02 tablespace dextertbs asselect level id , level || 'name' as name from dual connect by level <=10000;

alter table test01modify id primary key ;
alter table test02 add constraint fk_test02_id foreign key (id)references test01(id) ;

只看主表所在的表空间（因为关系是由子表来维护的，所以单独迁移主表所在的表空间没有影响）

sys@GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('dextbs',true) ;
PL/SQL procedure successfully completed.

sys@GG1> SELECT * FROMTRANSPORT_SET_VIOLATIONS;
VIOLATIONS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index DEXTER.SYS_C0011314 in tablespace USERS enforces primaryconstraints of table DEXTER.TEST01 in tablespace DEXTBS.

如上所示

主键的索引SYS_C0011314在users表空间里面，不是自包含。

有两种解决办法

1. 包含users表空间

2. 将索引move到dextbs表空间中

sys@GG1> alter index DEXTER.SYS_C0011314rebuild tablespace dextbs ;

Index altered.

sys@GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('dextbs',true) ;

PL/SQL procedure successfully completed.

sys@GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

这个时候说明只迁移主表空间dextbs是可以的，没有任何问题。

我们看只包括子表的表空间

sys@GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('dextertbs',true) ;

PL/SQL procedure successfully completed.

sys@GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39906: Constraint FK_TEST02_ID between table DEXTER.TEST01 in tablespaceDEXTBS and table DEXTER.TEST02 in tablespace DEXTERTBS.

这里就因为外键的关系有报错信息了。

sys@GG1> EXECUTEDBMS_TTS.TRANSPORT_SET_CHECK('dextertbs,dextbs',true) ;

PL/SQL procedure successfully completed.

sys@GG1> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

ok 没问题了，下面就可以迁移dextertbs , dextbs 两个表空间了

sys@GG1> alter tablespace dextbs read only ;

Tablespace altered.

sys@GG1> alter tablespac