sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle expdp impdp >

【学习笔记】Oracle逻辑导入导出 出现乱码的原理和解决办法

时间:2016-07-26 07:57   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映数据库使用逻辑导出导入exp/imp的方法迁移数据时出现筹码与字符长度变长问题,分析原因为字符编码问题导致,通过ZHS16GBK和AL32UTF8字符编码分析exp/imp。

一、试验环境和试验准备工作

1.源端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/oracleplus

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 oracleplus (name varchar2(6));

Table created.

SQL> insert into oracleplus values('Oracle研究中心');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from oracleplus;

NAME
------
Oracle研究中心
Oracle研究中心
Oracle研究中心
Oracle研究中心

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

二、导出测试表(oracleplus)

1.ZHS16GBK编码

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ exp hr/oracleplus file=/tmp/oracleplus.dmp \
log=/tmp/oracleplus.log tables=oracleplus

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                       oracleplus          4 rows exported
Export terminated successfully without warnings.

2.AL32UTF8编码

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ exp hr/oracleplus file=/tmp/oracleplus_new.dmp \
> log=/tmp/oracleplus_new.log tables=oracleplus

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                       oracleplus          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 oracleplus.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 oracleplus_new.dmp
虽然导出客户端设置了不步的编码,但是导出的文件大小相等

3.2)文件头部比较

--oracleplus.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/oracleplus.dm
00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p...............

--oracleplus_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/oracleplus_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文件内容

--oracleplus.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 "oracleplus".
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 "oracleplus" (
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 ; ....Oracle研究中心....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....Oracle研究中心..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..Oracle研究中心..

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle逻辑导入导出 出现乱码的原理和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/896.html

Oracle研究中心

关键词:

Oracle exp imp出现乱码

数据库逻辑导出导入筹码的解决办法

通过ZHS16GBK和AL32UTF8字符编码分析exp/imp

数据imp导入后字符变长