ORA-12899 采用exp导入zhs16gbk双字节字符到utf8三字节字符

http://space.itpub.net/9240380/viewspace-670056
http://www.itpub.net/thread-339220-1-2.html


IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "DDD"."DDD_PROCLAM"."DDD_TYPE_NAME" (actual: 27, maximum: 20)


自己也测试了下

比如对于汉字：

勋杨



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