采用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

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-670056/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-670056/