採用exp匯入zhs16gbk雙位元組字元到utf8三位元組字元

  源於看到itpub sundog315大牛的貼子，

http://www.itpub.net/thread-339220-1-2.html

 

自己也測試了下

比如對於漢字：

    勳楊

 

utf8  字符集

SQL> select dump('勳楊') from dual;

DUMP('勳楊')
-------------------------------------
Typ=96 Len=6: 229,139,139,230,157,168  ##佔用6個位元組，後跟6個數字喲

 

 

zhs16gbk字符集

SQL> select dump('勳楊') from dual;

DUMP('勳楊')
--------------------------------------------------------------------------------
Typ=96 Len=4: 209,171,209,238  ##佔用4個位元組，後跟4個數字喲

 

 

 

 

我在zhs16gbk字符集資料庫中建立一個mig使用者的表test

SQL> create table test(a int,b varchar2(4));--注意：這裡長度是4，因為zhs16gbk漢字佔用2個位元組

Table created.

SQL> insert into test values(1,'勳楊');##插入測試資料

 

然後用imp把以上資料匯入到utf8的資料庫中，就報

 

-bash-3.1$ imp userid=system/system fromuser=mig touser=mig file=/oracle/exp_mig.dmp  log=imp_two_set.log

Import: Release 10.2.0.4.0 - Production on 星期六 7月 31 10:22:25 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing MIG's objects into MIG
. . importing table                         "TEST"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "MIG"."TEST"."B" (actual: 6, maximum: 4)
Column 1 1
Column 2 勳楊          0 rows imported
Import terminated successfully with warnings.

###上面報長度不對，就是因為不同字符集對於漢字的儲存長度不同，佔用長度不同

 

所以只能改寫dmp檔案涉及到varchar,varchar2,char的地方，本文就直接用sed修改dmp

strings exp_mig.dmp  ##先大約檢視下出錯的地方

sed -i 's/VARCHAR2(4)/VARCHAR2(6)/g' exp_mig.dmp

再次從zhs16gbk到utf8 imp資料,就好了

-bash-3.1$ imp userid=system/system fromuser=mig touser=mig file=/oracle/exp_mig.dmp  log=imp_two_set.log

Import: Release 10.2.0.4.0 - Production on 星期六 7月 31 10:30:41 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing MIG's objects into MIG
. . importing table                         "TEST"          1 rows imported
Import terminated successfully without warnings.
-bash-3.1$ sqlplus mig/system

SQL*Plus: Release 10.2.0.4.0 - Production on 星期六 7月 31 10:30:43 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from test;

         A B
---------- ------
         1 勳楊

SQL> exit