0、准备工作 --创建被传输的表空间 sql create tablespace tbs_single datafile c:\oracle\oradata\ora9i\tbs_single01.dbf size 100m extent management local; --创建用户，并将表空间作为默认表空间 sql create user tranp identified by oracle default t
0、准备工作
--创建被传输的表空间
sql> create tablespace tbs_single
datafile 'c:\oracle\oradata\ora9i\tbs_single01.dbf' size 100m
extent management local;
--创建用户，并将表空间作为默认表空间
sql> create user tranp identified by oracle default tablespace tbs_single;
sql> grant connect,resource to tranp;
--在该表空间创建表，用于测试
sql> create table tranp.t01 as select * from sys.dba_objects;
1、检查源、目标平台endianness
在源数据库平台上：
sql> select d.platform_name, endian_format
2 from v$transportable_platform. tp, v$database d
3 where tp.platform_name = d.platform_name;
platform_name endian_format
------------------------------------ --------------
microsoft windows ia (32-bit) little
在目标数据库平台上：
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 ia (32-bit) little
由于源和目标平台的endianness一致，可以省去convert这一步。
2、检查要表空间是否自包含
sql> execute dbms_tts.transport_set_check('tbs_single',true);
pl/sql 过程已成功完成。
sql> select * from transport_set_violations;
未选定行
说明表空间是自包含的。
3、产生传输表空间集
创建目录对象
sql> create directory tranp_dir as 'c:\software';
目录已创建。
sql> grant read,write on directory tranp_dir to public;
授权成功。
将表空间置为只读。
sql> alter tablespace tbs_single read only;
表空间已更改。
使用数据泵导出传输表空间的元数据
注：如果endianness不一致，还需要使用rman进行转换表空间的数据文件。
4、传送表空间集
将表空间的数据文件和导出的dmp文件，传送到目标数据库平台上。
5、导入表空间
在目标数据库中，创建相应的目录对象和用户。
sql> create directory tranp_dir as '/home/oracle';
directory created.
sql> grant read,write on directory tranp_dir to public;
grant succeeded.
sql> create user tranp identified by oracle;
user created.
sql> grant connect,resource to tranp;
grant succeeded.
使用数据库泵，导入到目标数据库中。
[oracle@ocmu ~]$ impdp system/oracle dumpfile=tbsingle.dmp directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/prod/tbs_single01.dbf remap_schema=tranp:tranp
import: release 10.2.0.1.0 - production on thursday, 30 august, 2012 23:40:25
copyright (c) 2003, 2005, oracle. all rights reserved.
connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
master table system.sys_import_transportable_01 successfully loaded/unloaded
starting system.sys_import_transportable_01: system/******** dumpfile=tbsingle.dmp directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/prod/tbs_single01.dbf remap_schema=tranp:tranp
processing object type transportable_export/plugts_blk
processing object type transportable_export/table
processing object type transportable_export/post_instance/plugts_blk
job system.sys_import_transportable_01 successfully completed at 23:40:29
将被导入的表空间置为read write。
sql> alter tablespace tbs_single read write;
tablespace altered.
6、测试
目标库中，进行测试。
sql> select name from v$tablespace;
name
------------------------------
system
undotbs1
sysaux
users
temp
tbs_single
。。。。。。。。。。。。。。。。。
13 rows selected.
sql> conn tranp/oracle
connected.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
t01 table
sql> select count(*) from t01;
count(*)
----------
49795
sql> conn / as sysdba
connected.
sql> select tablespace_name,status from dba_tablespaces;
tablespace_name status
------------------------------ ---------
system online
undotbs1 online
sysaux online
temp online
users online
tbs_single online
。。。。。。。。。。。。。。。。。。。。。。。。
13 rows selected.
7、问题
问题描述：
oracle@ocmu ~]$ impdp system/oracle dumpfile=tbsingle.dmp directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/prod/tbs_single01.dbf remap_schema=tranp:tranp
import: release 10.2.0.1.0 - production on thursday, 30 august, 2012 23:25:47
copyright (c) 2003, 2005, oracle. all rights reserved.
connected to: oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
master table system.sys_import_transportable_01 successfully loaded/unloaded
starting system.sys_import_transportable_01: system/******** dumpfile=tbsingle.dmp directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/prod/tbs_single01.dbf remap_schema=tranp:tranp
processing object type transportable_export/plugts_blk
ora-39123: data pump transportable tablespace job aborted
ora-29345: cannot plug a tablespace into a database using an incompatible character set
job system.sys_import_transportable_01 stopped due to fatal error at 23:25:53
a元数据库：
sql> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select value$ aa from sys.props$ where name='nls_language')tab1,
3 (select value$ bb from sys.props$ where name='nls_iso_currency')tab2,
4 (select value$ cc from sys.props$ where name='nls_characterset')tab3;
tab1.aa||'_'||tab2.bb||'.'||tab3.cc
----------------------------------------------------------------------
american_america.zhs16gbk
b目标数据库：
sql> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select value$ aa from sys.props$ where name='nls_language')tab1,
3 (select value$ bb from sys.props$ where name='nls_iso_currency')tab2,
4 (select value$ cc from sys.props$ where name='nls_characterset')tab3;
tab1.aa||'_'||tab2.bb||'.'||tab3.cc
----------------------------------------------------------------------
american_america.al32utf8
原因：由于源和目标数据库的字符集不一致或不是子集和超集的关系，所以造成不能导入表空间的字符集。
解决方法：将源数据库和目标数据库的字符集调整为一致的字符集；或者源数据库的字符集是目标数据库的子集。
源数据库的修改：
sql> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
第1行出现错误:
ora-12719:操作要求数据库处于restricted模式下
sql> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
oracle例程已经关闭。
sql> startup mount;
oracle例程已启动
total system global area 444596224 bytes
fixed size 1219904 bytes
variable size 138412736 bytes
database buffers 301989888 bytes
redo buffers 2973696 bytes
数据库已装载
sql> alter system enable restricted session;
系统已更改
sql> alter database open;
数据库已更改
sql> alter database character set internal_use utf8;
数据库已更改
sql> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
oracle例程已经关闭。
sql> startup mount;
oracle例程已启动
total system global area 444596224 bytes
fixed size 1219904 bytes
variable size 138412736 bytes
database buffers 301989888 bytes
redo buffers 2973696 bytes
数据库已装载
sql> alter system disable restricted session;
系统已更改
sql> alter database open;
数据库已更改
sql> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select value$ aa from sys.props$ where name='nls_language')tab1,
3 (select value$ bb from sys.props$ where name='nls_iso_currency')tab2,
4 (select value$ cc from sys.props$ where name='nls_characterset')tab3;
tab1.aa||'_'||tab2.bb||'.'||tab3.cc
----------------------------------------------------------------------
american_america.utf8
目标数据库修改：
sql> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
error at line 1:
ora-12719: operation requires database is in restricted mode
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 444596224 bytes
fixed size 1219904 bytes
variable size 138412736 bytes
database buffers 301989888 bytes
redo buffers 2973696 bytes
database mounted.
sql> alter system enable restricted session;
system altered.
sql> alter database open;
database altered.
sql> alter database character set internal_use utf8;
database altered.
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 444596224 bytes
fixed size 1219904 bytes
variable size 138412736 bytes
database buffers 301989888 bytes
redo buffers 2973696 bytes
database mounted.
sql> alter system disable restricted session;
system altered.
sql> alter database open;
database altered.
sql> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select value$ aa from sys.props$ where name='nls_language')tab1,
3 (select value$ bb from sys.props$ where name='nls_iso_currency')tab2,
4 (select value$ cc from sys.props$ where name='nls_characterset')tab3;
tab1.aa||'_'||tab2.bb||'.'||tab3.cc
----------------------------------------------------------------------
american_america.utf8
修改完成，再重新导出/导入一遍，即可成功。