0、準備工作 –創(chuàng)建被傳輸?shù)谋砜臻g SQL create tablespace tbs_single datafile c:oracleoradataora9itbs_single01.dbf size 100M extent management local; –創(chuàng)建用戶，并將表空間作為默認表空間 SQL create user tranp identified by Oracle default t
0、準備工作
–創(chuàng)建被傳輸?shù)谋砜臻g
SQL> create tablespace tbs_single
datafile ‘c:Oracleoradataora9itbs_single01.dbf’ size 100M
extent management local;
–創(chuàng)建用戶，并將表空間作為默認表空間
SQL> create user tranp identified by Oracle default tablespace tbs_single;
SQL> grant connect,resource to tranp;
–在該表空間創(chuàng)建表，用于測試
SQL> create table tranp.t01 as select * from sys.dba_objects;
1、檢查源、目標平臺Endianness
在源數(shù)據(jù)庫平臺上：
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
在目標數(shù)據(jù)庫平臺上：
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、產(chǎn)生傳輸表空間集
創(chuàng)建目錄對象
SQL> create DIRECTORY tranp_dir as ‘c:software’;
目錄已創(chuàng)建。
SQL> grant read,write on DIRECTORY tranp_dir to public;
授權(quán)成功。
將表空間置為只讀。
SQL> alter tablespace tbs_single read only;
表空間已更改。
使用數(shù)據(jù)泵導(dǎo)出傳輸表空間的元數(shù)據(jù)
注：如果Endianness不一致，還需要使用RMAN進行轉(zhuǎn)換表空間的數(shù)據(jù)文件。
4、傳送表空間集
將表空間的數(shù)據(jù)文件和導(dǎo)出的DMP文件，傳送到目標數(shù)據(jù)庫平臺上。
5、導(dǎo)入表空間
在目標數(shù)據(jù)庫中，創(chuàng)建相應(yīng)的目錄對象和用戶。
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.
使用數(shù)據(jù)庫泵，導(dǎo)入到目標數(shù)據(jù)庫中。
[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
將被導(dǎo)入的表空間置為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元數(shù)據(jù)庫：
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目標數(shù)據(jù)庫：
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
原因：由于源和目標數(shù)據(jù)庫的字符集不一致或不是子集和超集的關(guān)系，所以造成不能導(dǎo)入表空間的字符集。
解決方法：將源數(shù)據(jù)庫和目標數(shù)據(jù)庫的字符集調(diào)整為一致的字符集；或者源數(shù)據(jù)庫的字符集是目標數(shù)據(jù)庫的子集。
源數(shù)據(jù)庫的修改：
SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
第1行出現(xiàn)錯誤:
ORA-12719:操作要求數(shù)據(jù)庫處于RESTRICTED模式下
SQL> shutdown immediate;
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
Oracle例程已經(jīng)關(guān)閉。
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
數(shù)據(jù)庫已裝載
SQL>? alter system enable restricted session;
系統(tǒng)已更改
SQL> alter database open;
數(shù)據(jù)庫已更改
SQL> alter database character set internal_use utf8;
數(shù)據(jù)庫已更改
SQL> shutdown immediate;
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
Oracle例程已經(jīng)關(guān)閉。
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
數(shù)據(jù)庫已裝載
SQL>? alter system disable restricted session;
系統(tǒng)已更改
SQL> alter database open;?
數(shù)據(jù)庫已更改
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
目標數(shù)據(jù)庫修改：
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
修改完成，再重新導(dǎo)出/導(dǎo)入一遍，即可成功。
