再次接触tts的特性

可传输表空间tts用于平台数据传递，在10g中可以用于不同的平台传递，当两个平台的字节顺序不同时，也可以利用rman转换而后传递 ，相对数据库层面的导入导出 数据泵 sqlldr等来说采用的os的复制迁移，然后倒入元数据到目标数据库，效率上提高了许多。

SQL> conn xiaoyu/xiaoyu

Connected.

SQL> create tablespace tts datafile '/nfs/data/tts01.dbf' size 100m;

Tablespace created.

SQL> create table tts01 tablespace tts as select * from dba_objects;

Table created.

SQL> col directory_path for a30

SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

------------------------------ ------------------------------

BACK /db

WORK_DIR /ade/aime_ship_10gR2_050630.00

22/oracle/work

DATA_PUMP_DIR /db/oracle10g/product/10.2.0/d

b/rdbms/log/

NFS_DMP /nfs

ADMIN_DIR /ade/aime_ship_10gR2_050630.00

22/oracle/md/admin

SQL> create or replace directory tts as '/home/oracle';

Directory created.

SQL> alter tablespace tts read only;

Tablespace altered.

SQL> execute dbms_tts.transport_set_check('tts',true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

[oracle@hebs_sjz_ga_ora ~]$ expdp '"sys/oracle as sysdba"' dumpfile=tts.dmp logfile=tts.log directory=tts transport_tablespaces=tts

Export: Release 10.2.0.1.0 - 64bit Production on Monday, 10 September, 2012 17:14:53

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=tts.dmp logfile=tts.log directory=tts transport_tablespaces=tts

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:

/home/oracle/tts.dmp

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:14:58

[oracle@hebs_sjz_ga_ora ~]$ ls -l tts*

-rw-r----- 1 oracle oinstall 77824 09-10 17:14 tts.dmp

-rw-r--r-- 1 oracle oinstall 924 09-10 17:14 tts.log

如果平台的字节顺序不同，就需要进行转换，可以在源平台转换也可以在目标平台转换。比如需要将上述表空间转换到HP-UX（64-bit），可以用rman进行如下转换：

Rman>convert tablespace tts to platform ‘HP-UX(64-bit)’ format =’/home/oracle/tts01,dbf’

Convert tablespace用于在源平台转换，convert datafile用于在目标平台转换。

通过操作系统的工具先把数据文件和元数据复制到目标平台上，再利用impdp进行数据导入。

[oracle@hebs_sjz_zh_ga_one ~]$ impdp '"sys/oracle as sysdba"' dumpfile=tts.dmp logfile=tts.log transport_datafiles=tts01.dbf directory=back remap_schema=cobra:xiaoyu

Import: Release 10.2.0.1.0 - 64bit Production on Monday, 10 September, 2012 18:24:37

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=tts.dmp logfile=tts.log transport_datafiles=tts01.dbf directory=back remap_schema=xiaoyu:cobra

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-01565: error in identifying file 'tts01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 18:24:39

这有个很棘手的错误，就是transport_datafiles确实在该directory的目录下，还是提示无法找到，其实这里需要使用绝对路径制定transport_datafiles的位置。

[oracle@hebs_sjz_zh_ga_one ~]$ impdp '"sys/oracle as sysdba"' dumpfile=tts.dmp logfile=tts.log transport_datafiles=/home/oracle/tts01.dbf directory=back remap_schema= cobra:xiaoyu

Import: Release 10.2.0.1.0 - 64bit Production on Monday, 10 September, 2012 18:24:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" dumpfile=tts.dmp logfile=tts.log transport_datafiles=/home/oracle/tts01.dbf directory=back remap_schema=xiaoyu:cobra

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 18:24:52

SQL> alter tablespace tts read write;

Tablespace altered.

SQL> select count(*) from cobra.tts01;

COUNT(*)

----------

50685

已经顺利的传输到目标数据库，Tts在跨操作系统平台和oracle版本时非常有效（跨越oracle的版本迁移已经测试过，可以正常运行。）

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1059396/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1059396/