联系：手机/微信(+86 17813235971) QQ(107644445)
标题：通过ZHS16GBK和AL32UTF8字符编码分析exp/imp
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、试验环境和试验准备工作
1.源端
[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/xifenfei
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:24 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> create table xifenfei (name varchar2(6));
Table created.
SQL> insert into xifenfei values('惜分飞');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xifenfei;
NAME
------
惜分飞
惜分飞
惜分飞
惜分飞
2.目标端
[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 15:26:18 2011
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
二、导出测试表(xifenfei)
1.ZHS16GBK编码
[oracle@node1 ~]$ echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK [oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei.dmp \ log=/tmp/xifenfei.log tables=xifenfei Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:11 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... . . exporting table XIFENFEI 4 rows exported Export terminated successfully without warnings.
2.AL32UTF8编码
[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 [oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei_new.dmp \ > log=/tmp/xifenfei_new.log tables=xifenfei Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:38:14 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set server uses ZHS16GBK character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table XIFENFEI 4 rows exported Export terminated successfully without warnings.
3.dmp文件比较
3.1)文件大小比较
[oracle@node1 ~]$ cd /tmp [oracle@node1 tmp]$ ll *.dmp -rw-r--r-- 1 oracle oinstall 16384 12-21 14:07 xifenfei.dmp -rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 xifenfei_new.dmp 虽然导出客户端设置了不步的编码，但是导出的文件大小相等
3.2)文件头部比较
--xifenfei.dmp 00000000h: 03 03 54 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..TEXPORT:V10.02 00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES. 00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 54 03 54 ; 8192.0.32.0..T.T 00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 11 00 20 ; .?............ 00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ; 00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ; 00000060h: 20 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 ; Wed Dec 00000070h: 20 32 31 20 31 34 3A 37 3A 31 32 20 32 30 31 31 ; 21 14:7:12 2011 00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 2E 64 6D ; /tmp/xifenfei.dm 00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p............... --xifenfei_new.dmp 00000000h: 03 03 69 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..iEXPORT:V10.02 00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES. 00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 69 03 54 ; 8192.0.32.0..i.T 00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 15 00 20 ; .?............ 00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ; 00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ; 00000060h: 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 20 ; Wed Dec 00000070h: 32 31 20 31 34 3A 33 38 3A 31 35 20 32 30 31 31 ; 21 14:38:15 2011 00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 5F 6E 65 ; /tmp/xifenfei_ne 00000090h: 77 2E 64 6D 70 00 00 00 00 00 00 00 00 00 00 00 ; w.dmp........... --ZHS16GBK dmp 文件 第2,3字节为 0354 --AL32UTF8 dmp 文件 第2,3字节为 0369
3.3)比较dmp文件内容
--xifenfei.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6)) PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ; 40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ; integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A ; T.EXIT.
--xifenfei_new.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6)) PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ; 40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ; integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A ; T.EXIT.
--通过比较知道，不同客户端编码，导出的数据内容相同，
--都是一个汉字对应两个字节(说明是按照服务端编码导出)
三、导入测试表(xifenfei)
1.导入xifenfei.dmp(ZHS16GBK编码导出)
1.1)客户端编码设置ZHS16GBK
[oracle@node1 ~]$ echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK [oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \ log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:16:27 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by HR, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing HR's objects into TEST . . importing table "XIFENFEI" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 0 rows imported Import terminated successfully with warnings.
1.2)客户端编码设置为AL32UTF8
[oracle@node1 ~]$ echo $NLS_LANG AMERICAN_AMERICA.AL32UTF8 [oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \ log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:21:41 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by HR, not by you import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export client uses ZHS16GBK character set (possible charset conversion) . importing HR's objects into TEST . . importing table "XIFENFEI" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 0 rows imported Import terminated successfully with warnings.
2.导入xifenfei_new.dmp(AL32UTF8编码导出)
2.1)客户端编码设置ZHS16GBK
[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK [oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \ > log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:39:53 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by HR, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) export client uses AL32UTF8 character set (possible charset conversion) . importing HR's objects into TEST . . importing table "XIFENFEI" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 惜分飞 0 rows imported Import terminated successfully with warnings.
2.2)客户端编码设置为AL32UTF8
[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 [oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \ > log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:41:12 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by HR, not by you import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing HR's objects into TEST . . importing table "XIFENFEI" IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6) Column 1 鎯滃垎椋 0 rows imported Import terminated successfully with warnings.
四、修改列长度，导入成功
无论源端客户端使用何种编码导出，目标端使用何种编码导入(仅限我这里说的AL32UTF8和ZHS16GBK),如果客户端编码是ZHS16GBK，验证数据的时候，可以省略掉设置编码的过程。
--导入报错后，登录数据库，修改列长度，因为目标端数据库编码是AL32UTF8， --1个汉字占用3个字节修改列的程度满足错误提示的最大程度。 SQL> alter table xifenfei modify name varchar2(9); Table altered. [oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \ log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test IGNORE=y Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:25:07 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by HR, not by you import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export client uses ZHS16GBK character set (possible charset conversion) . importing HR's objects into TEST . . importing table "XIFENFEI" 4 rows imported Import terminated successfully without warnings. --因为目标端客户端编码为AL32UTF8，而linux展示平台编码为gbk，所以出现乱码 --修改客户端编码后，查询乱码问题消失 [oracle@node1 ~]$ sqlplus test/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:14 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select * from xifenfei; NAME --------- 鎯滃垎椋 鎯滃垎椋 鎯滃垎椋 鎯滃垎椋 SQL> exit [oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK [oracle@node1 ~]$ sqlplus test/xifenfei SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:52 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select * from xifenfei; NAME ------------------ 惜分飞 惜分飞 惜分飞 惜分飞
五、试验总结
1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持
2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储
3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致