本来没想过发布这个文章，只是周边有一朋友工作中遇到合并数据库的情况，他是通过expdp提取出五个库对象，然后impdp到新库里面。我觉得这种方法特别耗时，尤其在数据量比较大的时候。这种时候我觉得采用表空间传输方法效率更高点，当然我们还可以使用RMAN DUPLICATE方式。下面就把TRANSPORT TABLESPACE一测试案例贴出来分享。
传输表空间有三个基本要求：1）要传输的表空间是自包含的；2）要传输的表空间是只读的；3）源数据库与目标数据库的字符集相同。跨平台表空间传输还需要考虑字节顺序和平台。引用eygle的原文：数据文件之所以不能跨平台，主要是由于不同平台的字节顺序不同。在各种计算机体系结构中，由于对于字、字节等的存储机制有所不同，通信双方交流的信息单元(比特、字节、字、双字等)应该以什么样的顺序进行传送就成了一个问题，如果不达成一致的规则，通信双方将无法进行正确的编译码从而导致通信失败。 查看当前平台信息方法：Col platform_name for a40Set line 120SELECT A.platform_id, A.platform_name, B.endian_format FROM v$database A, v$transportable_platform B WHERE B.platform_id (+) = A.platform_id; 我们也可以通过数据库查询oracle支持的平台转换：sys@LUOCS11G> Col platform_name for a40sys@LUOCS11G> Select * from v$transportable_platform order by 3;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ---------------------------------------- ---------------------------- 3 HP-UX (64-bit) Big 6 AIX-Based Systems (64-bit) Big 18 IBM Power Based Linux Big 2 Solaris[tm] OE (64-bit) Big 4 HP-UX IA (64-bit) Big 16 Apple Mac OS Big 1 Solaris[tm] OE (32-bit) Big 9 IBM zSeries Based Linux Big 17 Solaris Operating System (x86) Little 19 HP IA Open VMS Little 20 Solaris Operating System (x86-64) Little 12 Microsoft Windows x86 64-bit Little 13 Linux x86 64-bit Little 8 Microsoft Windows IA (64-bit) Little 21 Apple Mac OS (x86-64) Little 11 Linux IA (64-bit) Little 5 HP Tru64 UNIX Little 10 Linux IA (32-bit) Little 7 Microsoft Windows IA (32-bit) Little 15 HP Open VMS Little20 rows selected. ====== TEST STARTING ======Source database: Windows 7 64 bit – 11.2.0.1 db version col platform_name for a40set line 120SELECT A.platform_id, A.platform_name, B.endian_format FROM v$database A, v$transportable_platform B WHERE B.platform_id (+) = A.platform_id;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ---------------------------------------- --------------------------- 12 Microsoft Windows x86 64-bit Little Destination Database: Linux 32 bit – 11.2.0.1 db version col platform_name for a40set line 120SELECT A.platform_id, A.platform_name, B.endian_format FROM v$database A, v$transportable_platform B WHERE B.platform_id (+) = A.platform_id;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ---------------------------------------- ---------------------------- 10 Linux IA (32-bit) Little Setup:SQL> create tablespace L datafile 'E:\APP\LUOCS\ORADATA\LUOCS\l01.dbf' size 10M;表空间已创建。SQL> grant resource, connect to l identified by oracle;授权成功。SQL> alter user l default tablespace l;用户已更改。SQL> conn l/oracle已连接。SQL> create table t1 ( 2 id number, 3 name varchar2(50) 4 );表已创建。SQL> conn / as sysdba已连接。SQL> insert into l.t1 2 select object_id, 3 object_name 4 from dba_objects 5 where rownum <= 10000 6 /已创建10000行。SQL> select count(*) from l.t1; COUNT(*)---------- 10000SQL> commit;提交完成。SQL> conn l/oracle已连接。SQL> create index idx_id on t1(id);索引已创建。Source database:SQL> conn / as sysdba已连接。SQL> exec dbms_tts.transport_set_check(ts_list => 'L', incl_constraints => TRUE, full_check => FALSE);PL/SQL 过程已成功完成。SQL> SELECT * FROM SYS.transport_set_violations;未选定行-- 满足自包含原则。SQL> alter tablespace l read only;表空间已更改。 导出要传输的表空间：C:\>exp '/ as sysdba' tablespaces=l transport_tablespace=y file=d:\l_exp.dmpExport: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:33:32 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服务器使用 AL32UTF8 字符集 (可能的字符集转换)注: 将不导出表数据 (行)即将导出可传输的表空间元数据...对于表空间 L.... 正在导出簇定义. 正在导出表定义. . 正在导出表 T1EXP-00091: 正在导出有问题的统计信息。. 正在导出引用完整性约束条件. 正在导出触发器. 结束导出可传输的表空间元数据导出成功终止, 但出现警告。SQL> alter tablespace l read write;表空间已更改。使用rman的convert命令转换文件格式：C:\>rman target /恢复管理器: Release 11.2.0.1.0 - Production on 星期二 9月 25 13:42:49 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.连接到目标数据库: LUOCS (DBID=578752031)RMAN> convert tablespace l to platform 'Linux IA (32-bit)' format 'd:\%N%f';启动 conversion at source 于 25-9月 -12使用目标数据库控制文件替代恢复目录分配的通道: ORA_DISK_1通道 ORA_DISK_1: SID=136 设备类型=DISK通道 ORA_DISK_1: 启动数据文件转换输入数据文件: 文件号=00005 名称=E:\APP\LUOCS\ORADATA\LUOCS\L01.DBF已转换的数据文件 = D:\L5通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01完成 conversion at source 于 25-9月 -12拷贝L5和l_exp.dmp文件到目标数据库服务器上。略！Destination Database:[oracle@localhost ~]$ ll L5 l_exp.dmp -rw-r--r-- 1 oracle oinstall 10493952 09-25 15:31 L5-rw-r--r-- 1 oracle oinstall 4096 09-25 15:31 l_exp.dmp[oracle@localhost ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 25 15:36:12 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: LUOCS11G (DBID=1468663667)RMAN> convert datafile '/home/oracle/L5' db_file_name_convert '/home/oracle/L5','/u01/app/oracle/oradata/luocs11g/L01.dbf';Starting conversion at target at 25-SEP-2012 15:38:07using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=69 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput file name=/home/oracle/L5converted datafile=/u01/app/oracle/oradata/luocs11g/L01.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01Finished conversion at target at 25-SEP-2012 15:38:09创建相应的用户：SQL> create user l identified by oracle;User created.SQL> grant create session, create table to l;Grant succeeded.执行导入：先确保客户端字符集：[oracle@localhost ~]$ echo $NLS_LANGAMERICAN_AMERICA.ZHS16GBK[oracle@localhost ~]$ imp \'/ as sysdba\' tablespaces=l transport_tablespace=y file=/home/oracle/l_exp.dmp datafiles=/u01/app/oracle/oradata/luocs11g/L01.dbfImport: Release 11.2.0.1.0 - Production on Tue Sep 25 15:42:21 2012Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathAbout to import transportable tablespace(s) metadata...import done in ZHS16GBK character set and AL16UTF16 NCHAR character setimport server uses AL32UTF8 character set (possible charset conversion). importing SYS's objects into SYS. importing SYS's objects into SYS. importing L's objects into L. . importing table "T1". importing SYS's objects into SYSImport terminated successfully without warnings.这时候表空间已经成功传输到目标数据库中：SQL> select name from v$datafile where name like '%L%';NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/luocs11g/L01.dbf再查数据：SQL> select count(*) from l.t1; COUNT(*)---------- 10000这时候导入的表空间为read only状态，确认无误可以改为读写模式：SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'L';TABLESPACE_NAME STATUS------------------------------------------------------------ ------------------L READ ONLYSQL> alter tablespace L read write;Tablespace altered.====== THE END ======后语：本测试案例中，我使用了exp/imp方式，当然我们完全可以使用expdp/impdp 数据泵来实现。 另外，如果不是跨平台跨字节顺序，那么传输Transport Tablespace操作简单了很多，所以选择此方法做数据迁移，值得推荐。下面是不跨平台的迁移测试案例：一、创建测试表空间SQL> create tablespace testdata datafile '/oradata/testdata01.dbf' size 10M;Tablespace created.SQL> create tablespace testindex datafile '/oradata/testindex01.dbf' size 5M;Tablespace created.SQL> conn jscn/jscnConnected.SQL> create table test (id number) tablespace testdata;Table created.SQL> create index ind_id on test(id) tablespace testindex;Index created.SQL> insert into test values(1);1 row created.SQL> insert into test values(2);1 row created.SQL> commit;Commit complete.二、测试开始1、检查支持的平台SQL> COLUMN PLATFORM_NAME FORMAT A32SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- -------------------------------- -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 10 Linux IA (32-bit) Little 6 AIX-Based Systems (64-bit) Big 3 HP-UX (64-bit) Big 5 HP Tru64 UNIX Little 4 HP-UX IA (64-bit) Big 11 Linux IA (64-bit) Little 15 HP Open VMS Little 8 Microsoft Windows IA (64-bit) LittlePLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- -------------------------------- -------------- 9 IBM zSeries Based Linux Big 13 Linux x86 64-bit Little 16 Apple Mac OS Big 12 Microsoft Windows x86 64-bit Little 17 Solaris Operating System (x86) Little 18 IBM Power Based Linux Big 20 Solaris Operating System (x86-64 Little ) 19 HP IA Open VMS Little19 rows selected.SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME--------------------------------------------------------------------------------ENDIAN_FORMAT--------------Linux x86 64-bitLittle2、Pick a Self-Contained Set of TablespacesSQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('testdata,testindex', TRUE);PL/SQL procedure successfully completed.SQL> select * from sys.transport_set_violations;no rows selected3、Generate a Transportable Tablespace Set3.1 Make all tablespaces in the set you are copying read-only. SQL> ALTER TABLESPACE testdata READ ONLY;Tablespace altered.SQL> ALTER TABLESPACE testindex READ ONLY;Tablespace altered.3.2Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.SQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';Directory created.[oracle@SHOW-100-12 ~]$ expdp system/sys DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=YExport: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 15:12:01Copyright (c) 2003, 2007, Oracle. All rights reserved.;;;Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********/ DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = testdata,testindex TRANSPORT_FULL_CHECK=YProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/dpump_dir/expdat0515.dmpJob "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:12:203.3 在目标中创建directory，拷贝文件到目标数据库文件[oracle@SHOW-100-12 oradata]$ scp test* 192.168.8.201:/oradata/Address 192.168.8.201 maps to bogon, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!oracle@192.168.8.201''s password:testdata01.dbf 100% 10MB 10.0MB/s 00:01testindex01.dbf 100% 5128KB 5.0MB/s 00:00[oracle@IM-8-201 ~]$ mkdir /home/oracle/dpump_dirSQL> CREATE OR REPLACE DIRECTORY dpump_dir AS '/home/oracle/dpump_dir';3.4 导入生成表空间[oracle@SHOW-100-12 oradata]$ impdp system/sys@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf REMAP_SCHEMA=jscn:securityImport: Release 10.2.0.4.0 - 64bit Production on Tuesday, 15 May, 2012 16:44:51Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********/@8.201 DUMPFILE=expdat0515.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES=/oradata/testdata01.dbf,/oradata/testindex01.dbf REMAP_SCHEMA=jscn:securityProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:21:47注意：这里要求目标数据库和源数据库字符集必须相同如何修改字符集，参考如下http://blog.csdn.net/rulev5/article/details/70201343.5 验证数据[oracle@IM-8-201 dpump_dir]$ sqlplus / as sysdbaSQL> conn security/securitySQL> select * from test; ID---------- 1 2