SQL> conn / as sysdba
已连接。
SQL> select * from  v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 TEST                           YES NO  YES

已选择7行。

SQL> select table_name from dba_tables where tablespace_name='TEST';

TABLE_NAME
------------------------------
REPAIR_TABLE
ORPHAN_KEY_TABLE
EMP3

SQL> select count(*) from scott.emp3;

  COUNT(*)
----------
     43008

SQL> alter tablespace test read only;

表空间已更改。

C:\Documents and Settings\oracle>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 13:38:09

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29341: 可传输集不是自包含的

作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 因致命错误于 13:38:16 停止

SQL> EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Partitioned table SCOTT.EMP4$REORG is partially contained in the transportable set: check table partitions by querying s
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Partitioned table SCOTT.EMP4 is partially contained in the transportable set: check table partitions by querying sys.dba
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Sys owned object  ORPHAN_KEY_TABLE in tablespace TEST not allowed in pluggable set
Sys owned object  REPAIR_TABLE in tablespace TEST not allowed in pluggable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set

已选择8行。

发现表空间有很多与其他表的关联和表空间关联.

SQL> select segment_name,partition_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME                                                                      PARTITION_NAME
--------------------------------------------------------------------------------- -----------------------
EMP3
EMP4$REORG                                                                        EMP4_P2
EMP4                                                                              EMP4_P2
REPAIR_TABLE
ORPHAN_KEY_TABLE

修改分区的表空间:

SQL> ALTER TABLE "SCOTT"."EMP4$REORG" MOVE PARTITION "EMP4_P2" TABLESPACE "EXAMPLE" UPDATE INDEXES;

表已更改。

SQL> ALTER TABLE "SCOTT"."EMP4"  MOVE PARTITION "EMP4_P2" TABLESPACE "EXAMPLE" UPDATE INDEXES;

表已更改。

SQL> select segment_name,partition_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME                                                                      PARTITION_NAME
--------------------------------------------------------------------------------- -----------------------------
EMP3
REPAIR_TABLE
ORPHAN_KEY_TABLE
6.539
6.603

SQL> alter table ORPHAN_KEY_TABLE move tablespace system;

表已更改。

SQL> alter table REPAIR_TABLE  move tablespace system;

表已更改。

SQL>  EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set

已选择8行。

发现默认属性也影响导出,再次修改分区的存储表空间属性:

SQL> alter table scott.emp4 MODIFY DEFAULT ATTRIBUTES   tablespace example;

表已更改。

SQL> alter table scott.emp4$reorg MODIFY DEFAULT ATTRIBUTES   tablespace example;

表已更改。

SQL> EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 过程已成功完成。

SQL> SELECT * FROM transport_set_violations;

未选定行

SQL>

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 14:15:15

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 14:15:32 成功完成

拷贝TRANSPORT.DMP和datafile到目标数据库:

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT.DMP  transport_datafiles=D:\orac
le\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 14:43:18

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=IMPDIR:TR
ANSPORT.DMP transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-00721: 发行版 10.2.0.4.0 中的更改无法用于发行版 10.2.0.1.0

作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 14:43:25 停止

重新指定版本,在源数据库进行导出:

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.2.0.1.0'

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 14:57:34

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.2.0.1.0'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 14:57:41 成功完成

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT.DMP  transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 15:12:50

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=IMPDIR:TRANSPORT.DMP
transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-00721: 发行版 10.2.0.4.0 中的更改无法用于发行版 10.2.0.1.0

作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:12:58 停止

降低版本测试一下:

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport2.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.1.0'

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 15:38:19

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=DATA_PUMP_DIR:transport2.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.1.0'
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
  D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT2.DMP
作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 15:38:27 成功完成

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT2.DMP  transport_datafiles=D:\ora
cle\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 15:40:25

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=IMPDIR:TR
ANSPORT2.DMP transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-00721: 发行版 10.2.0.4.0 中的更改无法用于发行版 10.2.0.1.0

作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 15:40:32 停止

难道不能从高版本往低版本传输???