传输表空间

1.源库和目标库必须是同样的字符集和国家字符集 
show parameter nls
nls_language                         string      AMERICAN
nls_territory                        string      AMERICA

1.准备实验环境
SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 10m;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table test_trans(id number,content varchar2(100));

Table created.

SQL> insert into test_trans values(1,'Linux oel 2.6.18-164.el5PAE #1 SMP Thu Sep 3 02:28:20 EDT 2009 i686 i686 i386 GNU/Linux');

1 row created.

SQL> commit;

Commit complete.

SQL> conn scott/tiger
Connected.
SQL> create table tt tablespace test as select * from emp;

Table created.

SQL> conn / as sysdba
Connected.

SQL> col SEGMENT_NAME for a25
SQL> col SEGMENT_TYPE for a20
SQL> col owner for a20
SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name='TEST';

OWNER                SEGMENT_NAME              SEGMENT_TYPE
-------------------- ------------------------- --------------------
TEST                 TEST_TRANS                TABLE
SCOTT                TT                        TABLE

2.检查表空间test是否是自包含
SQL> exec dbms_tts.transport_set_check('test',full_check=>true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected
即检查该表空间中，所存储的对象，是否要依赖于其它的文件或其它表空间的对象
此处没有查到，就代表该表空间是自包含的

3.设置test表空间为只读模式
SQL> alter tablespace test read only;

Tablespace altered.

4.使用数据泵导出test表空间的元数据
[oracle@oel ~]$ expdp \'/ as sysdba\' directory=dump_dir dumpfile=test.dmp transport_tablespaces=test

Export: Release 11.2.0.1.0 - Production on Wed Jun 8 10:22:29 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" directory=dump_dir dumpfile=test.dmp transport_tablespaces=test 
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:
  /u03/dump_dir/test.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST:
  /u01/app/oracle/oradata/orcl/test.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:22:58

5.检查一下源数据 与 目标数据库平台上endian是否一致
在源和目标上都执行
SELECT tp.endian_format
FROM   v$transportable_platform tp, v$database d
WHERE  tp.platform_name = d.platform_name;
检查是否一致

6.复制通过 expdp导出的元数据文件test.dmp以及test表空间的所有的数据文件test.dbf到windows平台

7.在windows数据库上创建目录对象
SQL> create directory dump_dir as 'E:\download\trans';

目录已创建。

8.把test.dbf复制到WINDOWS数据库的数据文件所在的目录下

9.检查WINDOWS数据库，是否包含TEST表空间中，所有对象的所有者
LINUX数据库中，TEST表空间中，包含 TEST 、SCOTT两个用户的对象
SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name='TEST';

OWNER                SEGMENT_NAME              SEGMENT_TYPE
-------------------- ------------------------- --------------------
TEST                 TEST_TRANS                TABLE
SCOTT                TT                        TABLE

在WINDOWS数据库中查询
SQL> select username from dba_users where username in ('SCOTT','TEST');

USERNAME
------------------------------------------------------------
SCOTT

发现WINDOWS数据库没有TEST用户，把TEST用户创建出来
SQL> create user test identified by test;

用户已创建。

SQL> grant connect,resource to test;

授权成功。

10.在WINDOWS数据库中，导入TEST表空间的元数据
C:\Users\Administrator>impdp '/as sysdba' transport_datafiles='D:\app\Administrator\oradata\orcl\test.dbf' directory=dump_dir dumpfile=test.dmp

Import: Release 11.2.0.1.0 - Production on 星期三 6月 8 10:33:54 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" transport_dataf
iles='D:\app\Administrator\oradata\orcl\test.dbf' directory=dump_dir dumpfile=te
st.dmp
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已于 10:34:02 成功完成

11.在源数据库及目标数据库上，把TEST表空间改成读写模式
alter tablespace test read write;

12.在WINDOWS数据库上，检查TEST表空间中的内容
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 8 10:34:34 2016

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

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user scott identified by tiger account unlock;

用户已更改。

SQL> conn scott/tiger
已连接。
SQL> select * from tab;

TNAME                                                        TABTYPE
------------------------------------------------------------ --------------
BONUS                                                        TABLE
DEPT                                                         TABLE
EMP                                                          TABLE
SALGRADE                                                     TABLE
TT                                                           TABLE
SQL> conn test/test
已连接。
SQL> select * from tab;
TNAME                                                        TABTYPE
------------------------------------------------------------ --------------
TEST_TRANS                                                   TABLE
SQL> select * from test_trans;
        ID
----------
CONTENT
-------------------------------------------------------------------------

       1
Linux oel 2.6.18-164.el5PAE #1 SMP Thu Sep 3 02:28:20 EDT 2009 i686 i686 i386 GN

U/Linux
这里面要注意的是如果表空间是非自包含的，有两种处理方法：一是将关联项移到其他表空间，二是将关联项一并的传输
SQL> exec dbms_tts.transport_set_check('test',full_check=>true);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected