再次接觸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@]