【赛迪网－it技术报道】示例：准备两个数据库（ltdb和epras），从ltdb导出tsts表空间（用户ts,包含2个数据文件到epras。

详细的执行步骤：

1.ltdb导出可传输表空间(以sysdba执行导出)到dmp文件。

2.将ltdb的tsts表空间改为只读。

3.用操作系统命令复制数据文件和dmp文件到另一个位置给另一个数据库epras使用。

如果在一台服务器上，dmp文件可以不必复制，复制完成后，将ltdb的tsts表空间改为可读写。

4.在epras数据库创建ts用户（此时不必授权）。

5.导入可传输表空间(以sysdba执行导入)到dmp文件到epras数据库。

6.将epras的tsts表空间改为可读写，给ts用户授权。

好处很明显，和数据exp/imp相比，速度很快，dmp文件只包含表空间元数据10g支持不同平台的表空间传输，支持大头/小头操作系统之间的传输/*

在oracle以前的版本中,可传输的表空间的特性可以让表空间在相同的体系结构和操作系统之间迁移.

在 oracle 数据库 10g 中，这个局限消失了：只要操作系统字节顺序相同,您就可以在平台

之间传输表空间。一些操作系统（包括 windows）在低位内存地址中用最低有效字节存储

多字节二进制数据；因此这种系统被称为低地址低字节序。相反，其它的操作系统

（包括 solaris）将最高有效字节存储在低位内存地址中，因此这种系统被称为低地址高字节序。

当一个低地址高字节序的系统试图从一个低地址低字节序的系统中读取数据时，需要一个转换过程

― 否则，字节顺序将导致不能正确解释读取的数据。

不过，当在相同字节顺序的平台之间传输表空间时，不需要任何转换。

oracle 10g的表空间跨平台迁移，与oracle9i比较就是增加了一个使用rman进行的文件格式转换的过程。

实际上仅仅是转换了数据文件头的格式信息。

column platform_name format a30

检查可转换平台

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) little

9 ibm zseries based linux big

13 linux 64-bit for amd little

16 apple mac os big

12 microsoft windows 64-bit for amd little

17 solaris operating system (x86) little

18 ibm power based linux big

17 rows selected

检查源平台

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

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

solaris[tm] oe (64-bit) big

检查目标平台

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

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

microsoft windows ia (32-bit) little

检查待转换表空间

execute dbms_tts.transport_set_check('tswin1,tswin2', true);

pl/sql procedure successfully completed.

是否有冲突

select * from transport_set_violations;

no rows selected

运行rman

rman target /

convert tablespace tswin1,tswin2

to platform 'microsoft windows ia (32-bit)'

format '/tmp/rman/%u';

在以上的例子中，转换后的文件名难于辨认并很难与原始文件关联还可以使用其它格式来为数据文件命名。

convert tablespace tswin1,tswin2

to platform 'microsoft windows ia (32-bit)'

db_file_name_convert '/tmp/ltdb','/tmp/rman'

此处将在转换后保留文件名。

*/

======详细执行结果===============

1.ltdb导出

root@t2000 # su - oracle

sun microsystems inc. sunos 5.10 generic january 2005

$ oracle_sid=ltdb

$ export oracle_sid

$ sqlplus /nolog

sql*plus: release 10.2.0.1.0 - production on thu jun 15 15:54:18 2006

copyright (c) 1982, 2005, oracle. all rights reserved.

sql> conn / as sysdba

connected.

sql> create tablespace tsts datafile '/tmp/ltdb/tsts01.dbf' size 10m ,'/tmp/ltdb/tsts02.dbf' size 10m;

tablespace created.

sql> create user ts identified by ts default tablespace tsts;

user created.

sql> grant connect to ts;

grant succeeded.

sql> grant resource to ts;

grant succeeded.

sql> conn ts/ts

connected.

sql> create table t1(c1 varchar(255),c2 varchar(255));

table created.

sql> insert into t1 select sys_guid(),sys_guid() from dual connect by level <=10000;

10000 rows created.

sql> commit;

commit complete.

sql> select * from t1 where rownum<=5;

c1 c2

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

163fd8c45c092993e04400144f025bb6 163fd8c45c0a2993e04400144f025bb6

163fd8c45c0b2993e04400144f025bb6 163fd8c45c0c2993e04400144f025bb6

163fd8c45c0d2993e04400144f025bb6 163fd8c45c0e2993e04400144f025bb6

163fd8c45c0f2993e04400144f025bb6 163fd8c45c102993e04400144f025bb6

163fd8c45c112993e04400144f025bb6 163fd8c45c122993e04400144f025bb6

sql> conn system/sys

connected.

sql> alter tablespace tsts read only

sql> /

tablespace altered.

sql> ho

$ exp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp l transport_tablespace=y

export: release 10.2.0.1.0 - production on thu jun 15 16:20:29 2006

copyright (c) 1982, 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

export done in zhs16gbk character set and al16utf16 nchar character set

note: table data (rows) will not be exported

about to export transportable tablespace metadata...

for tablespace tsts ...

. exporting cluster definitions

. exporting table definitions

. . exporting table

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

export terminated successfully without warnings.

2.epras导入

=============================================

root@t2000 # su - oracle

sun microsystems inc. sunos 5.10 generic january 2005

$ sqlplus /nolog

sql*plus: release 10.2.0.1.0 - production on thu jun 15 16:26:11 2006

copyright (c) 1982, 2005, oracle. all rights reserved.

sql> conn / as sysdba

connected.

sql> ho

复制数据文件，此时数据库ltdb的tsts表空间还是read only

$ cp /tmp/ltdb/ts*dbf /oracle

建立目标数据库上的用户，如果不建立，导入时需要加touser=参数，否则会出错

sql> create user ts identified by ts;

user created.

sql> ho

sql> revoke connect from ts;

revoke succeeded.

sql> revoke resource from ts;

revoke succeeded.

sql> ho

$ imp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp transport_tablespace=y datafiles=/oracle/tsts01.dbf, /oracle/tsts02.dbf

import: release 10.2.0.1.0 - production on thu jun 15 18:24:06 2006

copyright (c) 1982, 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

export file created by export:v10.02.01 via conventional path

about to import transportable tablespace(s) metadata...

import done in zhs16gbk character set and al16utf16 nchar character set

. importing sys's objects into sys

. importing sys's objects into sys

. importing ts's objects into ts

. . importing table "t1"

. importing sys's objects into sys

import terminated successfully without warnings.

$ exit

sql> alter tablespace tsts read write;

tablespace altered.

sql> select table_name from all_tables where owner='ts';

table_name

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

t1

sql> grant connect to ts;

grant succeeded.

sql> grant resource to ts;

grant succeeded.

sql> conn ts/ts

connected.

sql> set lines 132

sql> set pages 9999

sql> col c1 format a40

sql> col c2 format a40

sql> select * from t1 where rownum<=5;

c1 c2

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

163fd8c45c092993e04400144f025bb6 163fd8c45c0a2993e04400144f025bb6

163fd8c45c0b2993e04400144f025bb6 163fd8c45c0c2993e04400144f025bb6

163fd8c45c0d2993e04400144f025bb6 163fd8c45c0e2993e04400144f025bb6

163fd8c45c0f2993e04400144f025bb6 163fd8c45c102993e04400144f025bb6

163fd8c45c112993e04400144f025bb6 163fd8c45c122993e04400144f025bb6

sql>