测试结论
1,Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (?文档 ID 1454872.1) 摘文如下：
It is not possible to transport the SYSTEM, SYSAUX, TEMP tablespaces or objects owned by the user SYS
Restriction : SYSTEM, SYSAUX, TEMP Tablespace Objects. It is not possible to transport the SYSTEM, SYSAUX, and TEMP tablespaces or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, triggers, dblinks, roles, procedures, synonyms, users, privileges, dimensions, directories, sequences, and temporary tables. These must be created separately from the TTS operation, with scripts or export/import.
Note: The following Database Schemas/Users are also treated as SYS: 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP', 'ORDDATA' are treated as objects owned by 'SYS' and not exported with TTS.
Reference : Oracle Database Administrator's Guide - Transporting Tablespaces Between Databases.
Affected Version : All versions


2,基于TTS传输表空间，不能传输SYS的内置数据库用户的对象，见上；
  不能传输 java class,callout,views,trigger,dblink,roles,procedure,synonyms,users,privilege,dimension,directories,sequence,temporary table


3,所以如果要使用TTS迁移数据库，上述的对象必须要在源数据库迁移前进行获取其定义，尔后在目标数据库重新创建即可


4，所以TTS不适用如果要迁移的数据库中各种对象类型比较多，且依赖关系复杂






5，迁移表空间的具体流程如下：
  5.1,在源数据库变更迁移表空间为只读




  5.2，在源数据库检查要迁移的表空间是否完整性且一致


  5.3,在源数据库采用EXPDP导出表空间


  5.4，在源数据库或目标数据库转换要迁移的源数据库表空间的数据文件为另一个目录的文件集，用于目标数据库的最终的数据文件


  5.5,把在源数据库导出的表空间DMP以及转换的源数据库数据文件SCP或传输到目标数据库的对应目录，为了简化，可以放到同一个DIRECTORY下


  5.6，在目标数据库基于源数据库创建迁移表空间的测试用户


  5.7，在目标数据库变更迁移后的表空间由只读为读写   以及 变更迁移数据库用户 默认表空间 为对应表空间


  5.8，验证迁移后表空间的数据一致性


  5.9，其它补充工作


  上述的前提工作为 源和目标数据库的字符集相同，COMPATIBLE相同，还有TIME ZONE相同




测试明细


1,数据库版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2，创建测试用户及测试表空间
SQL> create tablespace tbs_zxy datafile '/oracle/mygirl/tbs_zxy01.dbf' size 10m;


Tablespace created.


SQL> create user user_zxy identified by system default tablespace tbs_zxy account unlock;


User created.


SQL> grant resource,connect,dba to user_zxy;


Grant succeeded.


3，在测试用户构建测试表及存储过程
SQL> conn user_zxy/system
Connected.
SQL> create table t_zxy(a int);


Table created.


SQL> create procedure  proc_t_zxy
  2  as
  3  v_cnt integer;
  4  begin
  5  select count(*) into v_cnt from t_zxy;
  6  end;
  7  /


Procedure created.


4，查询测试表及存储过程的对象信息
SQL> r
  1* select object_type,object_name from user_objects


OBJECT_TYPE         OBJECT_NAME
------------------- ------------------------------
PROCEDURE           PROC_T_ZXY
TABLE               T_ZXY




5，查询数据库目录信息
[oracle@mygirl ~]$ pwd
/home/oracle




SQL> r
  1* select owner,directory_name,directory_path from dba_directories


OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            XMLDIR                         /oracle/product/11.2.0/db_1/rdbms/xml
SYS                            ORACLE_OCM_CONFIG_DIR          /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
SYS                            DATA_PUMP_DIR                  /oracle/admin/mygirl/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR2         /oracle/product/11.2.0/db_1/ccr/state




SQL> host ls -lh /oracle/admin/mygirl/dpdump/
total 61M
-rw-r-----. 1 oracle oinstall 100K May  5 14:36 dmpfile.dmp
-rw-r-----. 1 oracle oinstall  116 May  5 00:06 dp.log
-rw-r--r--. 1 oracle oinstall 2.4K May  5 14:36 impscrpt.sql
-rw-r--r--. 1 oracle oinstall 1.1K May  5 14:45 imp_test_tablespace.log
-rw-r-----. 1 oracle oinstall  21M May 26 11:52 tbs_father01.dbf
-rw-r-----. 1 oracle oinstall  21M May 26 11:52 tbs_mother01.dbf
-rw-r-----. 1 oracle oinstall  21M May 26 11:52 tbs_sun01.dbf


6，在传输表空间前变更表空间为 只读


SQL> alter tablespace tbs_zxy read only;


Tablespace altered.


7，基于传输表空间进行EXPDP


[oracle@mygirl ~]$ expdp \'sys/system as sysdba\'  dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transfort_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log
LRM-00101: unknown parameter name 'transfort_tablespaces'


[oracle@mygirl ~]$ expdp \'sys/system as sysdba\'  dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transport_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log


Export: Release 11.2.0.4.0 - Production on Fri May 26 13:17:27 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "sys/******** AS SYSDBA" dumpfile=expdp_tbs_zxy.dmp directory=data_pump_dir transport_tablespaces=tbs_zxy logfile=expdp_tbs_zxy.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS_ZXY:
  /oracle/mygirl/tbs_zxy01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri May 26 13:18:13 2017 elapsed 0 00:00:45


You have new mail in /var/spool/mail/oracle








[oracle@mygirl ~]$ ls -lh /oracle/admin/mygirl/dpdump/expdp*
-rw-r-----. 1 oracle oinstall  88K May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp
-rw-r--r--. 1 oracle oinstall 1.2K May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.log




8,传输导出的EXPDP表空间数据集到目标数据库服务器
[oracle@mygirl ~]$ sz /oracle/admin/mygirl/dpdump/expdp*
rz
Starting zmodem transfer.  Press Ctrl+C to cancel.
  100%      88 KB   88 KB/s 00:00:01       0 Errors
  100%       1 KB    1 KB/s 00:00:01       0 Errors




dbca -silent -createDatabase -templateName $ORACLE_HOME/assistants/dbca/templates/New_Database.dbt -gdbName other -sysPassword system -systemPassword system -emConfiguration NONE \
-disableSecurityConfiguration ALL -datafileDestination /oracle/mygirl -nodelist mygirl \
-characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -memoryPercentage 25  -databaseType OLTP  




9,源数据库字符集
  1* select parameter,value from v$nls_parameters where lower(parameter) like '%char%'


PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CHARACTERSET                                                 ZHS16GBK
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_NCHAR_CONV_EXCP                                              FALSE


SQL> show parameter compat


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
plsql_v2_compatibility               boolean     FALSE


SQL> select userenv('language') from dual;


USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK






10,检查源数据库迁移表空间的是否完整一致性
SQL> exec dbms_tts.transport_set_check('tbs_zxy',true);


PL/SQL procedure successfully completed.


SQL> select * from transport_set_violations;


no rows selected




11,在源数据库转换
[oracle@mygirl admin]$ mkdir -p tts_dir
[oracle@mygirl admin]$ cd tts_dir/
[oracle@mygirl tts_dir]$ pwd
/oracle/admin/tts_dir


12,在相同机器创建另一个other数据库
略




13,在源数据库转换要迁移表空间数据文件




[oracle@mygirl tts_dir]$ env|grep  SID
ORACLE_SID=mygirl
[oracle@mygirl tts_dir]$ rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 26 21:13:01 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: MYGIRL (DBID=2527996082)


RMAN> convert tablespace tbs_zxy db_file_name_convert='/oracle/mygirl/tbs_zxy01.dbf','/oracle/admin/tts_dir/tbs_zxy01.dbf';


Starting conversion at source at 26-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/oracle/mygirl/tbs_zxy01.dbf
converted datafile=/oracle/admin/tts_dir/tbs_zxy01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 26-MAY-17


[oracle@mygirl tts_dir]$ ls -l /oracle/admin/tts_dir/tbs_zxy01.dbf
-rw-r-----. 1 oracle oinstall 10493952 May 26 21:13 /oracle/admin/tts_dir/tbs_zxy01.dbf


14,在目标数据库创建测试用户
[oracle@mygirl tts_dir]$ export ORACLE_SID=other
[oracle@mygirl tts_dir]$ sqlplus '/as sysdba'


SQL*Plus: Release 11.2.0.4.0 Production on Fri May 26 21:14:43 2017


Copyright (c) 1982, 2013, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> 


SQL> create user user_zxy identified by system;  


User created.


SQL> grant resource,connect,dba to user_zxy;


Grant succeeded.


15,移动传输表空间导出的表空间DMP文件到目标数据库的对应目录
/oracle/admin/other/dpdump/


[oracle@mygirl tts_dir]$ ls -l /oracle/admin/mygirl/dpdump/*dmp
-rw-r-----. 1 oracle oinstall 102400 May  5 14:36 /oracle/admin/mygirl/dpdump/dmpfile.dmp
-rw-r-----. 1 oracle oinstall  90112 May 26 13:18 /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp


[oracle@mygirl tts_dir]$ cp  /oracle/admin/mygirl/dpdump/expdp_tbs_zxy.dmp  /oracle/admin/other/dpdump
You have new mail in /var/spool/mail/oracle
[oracle@mygirl tts_dir]$ ls -l /oracle/admin/other/dpdump
total 92
-rw-r-----. 1 oracle oinstall   116 May 26 20:01 dp.log
-rw-r-----. 1 oracle oinstall 90112 May 26 21:44 expdp_tbs_zxy.dmp






16,在目标数据库把传输表空间导入到目标数据库
[oracle@mygirl tts_dir]$ export ORACLE_SID=other


[oracle@mygirl tts_dir]$ impdp \'/as sysdba\' directory=DATA_PUMP_DIR  transport_datafiles='/oracle/admin/tts_dir/tbs_zxy01.dbf' dumpfile=expdp_tbs_zxy.dmp logfile=imp_nb.log


Import: Release 11.2.0.4.0 - Production on Fri May 26 21:47:58 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.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_DIR transport_datafiles=/oracle/admin/tts_dir/tbs_zxy01.dbf dumpfile=expdp_tbs_zxy.dmp logfile=imp_nb.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri May 26 21:48:08 2017 elapsed 0 00:00:06


17,在目标数据库 变更 迁移后的表空间由 只读为 读写，且变更测试用户 的默认用户为 迁移的表空间
SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS_ZXY                        READ ONLY


6 rows selected.


SQL> alter tablespace tbs_zxy read write;


Tablespace altered.


SQL> select tablespace_name,status from dba_tablespaces;


TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS_ZXY                        ONLINE


6 rows selected.


SQL> alter user user_zxy default tablespace tbs_zxy;


User altered.


SQL> 




18，在目标数据库验证数据一致性
SQL> conn user_zxy/system
Connected.


SQL> r
  1* select object_type,object_name from user_objects


OBJECT_TYPE         OBJECT_NAME
------------------- --------------------------------------------------
TABLE               T_ZXY