oracle字符集深入研究
oracle字符集导致乱码问题
今天是2013-12-04，今天深入研究一下oracle字符集的问题。
以前自己也遇到过 在使用exp/imp导出导入数据的时候存在乱码的情况，也在终端查看数据的时候显示乱码的情况，今天这篇笔记给自己拨开迷雾，在此感谢分享知识的人员。
我们在将数据存入 数据库 的时候通过数据库的字符集进行转换字符编码，然后存入数据库中，在数据库中字符集文件目录为（10G&11G)：$ORACE_HOME/nls/data中。
可以查看数据库字符集信息如下：
SQL> select * from props$ where name like 'NLS_%';NAME VALUE$ COMMENT$------------------------------ ---------------------------------------- ----------------------------------------------------------------------NLS_CSMIG_SCHEMA_VERSION 5 Character set migration utiltiy schema version #NLS_LANGUAGE AMERICAN LanguageNLS_TERRITORY AMERICA TerritoryNLS_CURRENCY $ Local currencyNLS_ISO_CURRENCY AMERICA ISO currencyNLS_NUMERIC_CHARACTERS ., Numeric charactersNLS_CHARACTERSET WE8ISO8859P1 Character setNLS_CALENDAR GREGORIAN Calendar systemNLS_DATE_FORMAT DD-MON-RR Date formatNLS_DATE_LANGUAGE AMERICAN Date languageNLS_SORT BINARY Linguistic definitionNAME VALUE$ COMMENT$------------------------------ ---------------------------------------- ----------------------------------------------------------------------NLS_TIME_FORMAT HH.MI.SSXFF AM Time formatNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp formatNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone formatNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone formatNLS_DUAL_CURRENCY $ Dual currency symbolNLS_COMP BINARY NLS comparisonNLS_LENGTH_SEMANTICS BYTE NLS length semanticsNLS_NCHAR_CONV_EXCP FALSE NLS conversion exceptionNLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character setNLS_RDBMS_VERSION 10.2.0.1.0 RDBMS version for NLS parameters21 rows selected.SQL>
在创建数据库的时候就需要指定字符集和国家字符集，字符集一旦选择后续改变会 存在相应风险，因此刚刚创建数据库时候选择合适的字符集尤为重要。
我们可以通过oracle自带的一个 oracle localle builder工具查数据库中字符集文件，对于linux或是unix该命令在$ORALE_HOME/nls/lbuilder/lbuilder，如我当前字符集为WE8ISO8859P1 ：
并且可以找到字符的映射表，正是由于映射表的存在才知道每个编码代表的含义：
例如：
SQL> select nls_charset_id('WE8ISO8859P1') from dual;NLS_CHARSET_ID('WE8ISO8859P1')------------------------------ 31SQL> select dump('rhys') from dual;DUMP('RHYS')-----------------------------Typ=96 Len=4: 114,104,121,115SQL> select to_char('114','xxxxx') from dual;TO_CHA------ 72SQL> select to_number('72','xxxxx') from dual;TO_NUMBER('72','XXXXX')----------------------- 114SQL> select chr(114) from dual;C-rSQL> select to_char('104','xxxx' ) from dual;TO_CH----- 68SQL> select to_char('114','xxxx'),to_char('104','xxxx'),to_char('121','xxxxxx'),to_char('115','xxxx') from dual;TO_CH TO_CH TO_CHAR TO_CH----- ----- ------- ----- 72 68 79 73
可以看一下映射表r正是72.
我们经常说的需要字符集转换主要涉及三个方面：
1、客户端应用字符集
2、NLS_LANG字符集
3、数据库本身字符集
对于客户端应用字符集如sql plus，cmd都是基于操作系统的，使其该怎么显示字符。
下面讨论NLS_LANG字符集和数据库字符集
刚刚说了数据库字符集在数据库中怎么查看可以查看相关视图如;database_properties,props$在数据库创建的时候就已经确定了。
但是对于客户端的NLS_LANG字符集我们可以进行设置如 ：
[oracle@tf lbuilder]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[oracle@tf lbuilder]$ env | grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.US7ASCII
[oracle@tf lbuilder]$
对于windows需要查看注册表进行修订。
在我们以前使用imp/exp导入导出数据的时候总会提示字符集的信息，并也提示了相应字符集的转换如下：
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
这是怎么回事呢？
答案是字符集不一致，import done 代表的导出该文件时设置的客户端字符集信息也 就是NLS_LANG，如果字符集不一致那么就会进行转换。
查看如下：
[oracle@tf lbuilder]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII[oracle@tf lbuilder]$ exp system/root file=us.dmp owner=scott rows=yExport: Release 10.2.0.1.0 - Production on Wed Dec 4 07:16:33 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses WE8ISO8859P1 character set (possible charset conversion)About to export specified users .... exporting pre-schema procedural objects and actions. exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user SCOTT About to export SCOTT's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions
当我们在进行导入的时候就出现了上边导入字符集转换的问题。
那么如何查看exp导出文件的字符集呢？字符集信息存在导出文件头的第2和第3个字节处，对于expdp会在dmp文件中写入字符集以及语言和区域信息如下：
[oracle@tf lbuilder]$ cat us.dmp | od -X |head -2
0000000 45010003 524f5058 31563a54 32302e30
0000020 0a31302e 53595344 0a4d4554 45535552
可以通过转化获得如下：
[oracle@tf lbuilder]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 4 07:20:19 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select nls_charset_name(1) from dual;
NLS_CHAR
--------
US7ASCII
SQL>
在客户端设置不同的NLS_LANG字符集会有不同的显示结果：
[oracle@tf lbuilder]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[oracle@tf lbuilder]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 4 07:23:15 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
---------
04-DEC-13
SQL> exit
[oracle@tf lbuilder]$ export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
[oracle@tf lbuilder]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on ?l? 12? 4 07:24:52 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
--------------
04-12? -13
SQL>
这里有两个问题export NLS_LANG=AMERICAN_AMERICA.US7ASCII其中amercan代表使用的语言，america代表所属地域，后边US7ASCII代表字符集
在显示的时候出现了一个？说明在显示的时候进行字符转换。下面讨论一下：
第一，NLS_LANG字符集和数据库字符集不相同。
那么当输入数据的时候字符的显示需要依靠客户端的应用字符集，但是导入数据库的时候需要通过nls_lang进行字符集转换，完了之后再将nls_lang转换后的字符集在在数据库中进行转换，如果数据库字符集不是nls_lang字符集的超集那么就会存在映射错误导致破坏了原始数据。当然，在转换过程中如果在数据库中得不到nls_lang转换后的编码映射关系则会有？代替其字符存入数据库。刚刚显示？也是一个逆向的转换过程。
第二：当nls_lang字符集和数据库字符集相同
那么这个时候直接将输入的字符通过nls-lang编码以后直接存与数据库中，不会在数据库 在进行进一步的转换。
那么这就存在一个注意地方，在进行数据库数据导入导出的时候最好设置NLS_LANG等于数据库的字符集，一个是加快导入速度，另一是避免导入数据的完整性。
那么当进行跨平台导入导出的时候，涉及到字符集转换，那么怎么确保哪些数据字符集在映射中存在问题呢？oracle提供了一个工具，那就是csscan，可以通过它进行判定，如果存在导入数据时破坏了数据的一部分完整性，那么可以通过手动的方式对这些数据在进行整理调整。
eg：
平台A字符集为：WE8ISO8859P1需要将数据库全部导入到B库字符集为：US7ASCII，使用csscan工具进行校验：
首先创建用户和数据字典对象：
[oracle@tf lbuilder]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on ?l? 12? 4 07:45:04 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> @?/rdbms/admin/csminst.sqlgrant READ on directory log_file_dir to system *ERROR at line 1:ORA-22930: directory does not existgrant READ on directory data_file_dir to system *ERROR at line 1:ORA-22930: directory does not existdrop user csmig cascade *ERROR at line 1:ORA-01918: user 'CSMIG' does not existPlease create password for user CSMIG:Enter value for csmig_passwd: rootold 1: create user csmig identified by &csmig_passwdnew 1: create user csmig identified by rootUser created.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.Grant succeeded.User altered.0 rows created.1 row updated.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.Table created.Synonym dropped.Synonym created.Grant succeeded.13 rows created.View created.Synonym dropped.Synonym created.View created.Synonym dropped.Synonym created.View created.Synonym dropped.Synonym created.View created.Synonym dropped.Synonym created.View created.Synonym dropped.Synonym created.View created.Synonym dropped.Synonym created.View created.View created.Commit complete.Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options[oracle@tf lbuilder]$
之后使用csscan工具进行校验，如校验一张表在数据移动时是否存在问题。
[oracle@tf lbuilder]$ csscan system/root table=scott.emp fromchar=WE8ISO8859P1 tochar=US7ASCII log=verify.log array=1024000 process=10Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on ?l? 12? 4 07:48:57 2013Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsEnumerating table to scan.... process 1 scanning SCOTT.EMP[AAAMmGAAEAAAAAhAAA]Creating Database Scan Summary Report...Creating Individual Exception Report...Scanner terminated successfully.[oracle@tf lbuilder]$
完成之后会产生三个日志文件：
[oracle@tf lbuilder]$ ls -l
total 72
drwxr-x--- 2 oracle oinstall 4096 Oct 10 22:43 jlib
-rwxr-xr-x 1 oracle oinstall 891 Oct 10 22:43 lbuilder
-rw-r--r-- 1 oracle oinstall 24576 Dec 4 07:17 us.dmp
-rw-r--r-- 1 oracle oinstall 1357 Dec 4 07:49 verify.log.err
-rw-r--r-- 1 oracle oinstall 489 Dec 4 07:49 verify.log.out
-rw-r--r-- 1 oracle oinstall 5661 Dec 4 07:49 verify.log.txt
[oracle@tf lbuilder]$
查看相关err内容：
[oracle@tf lbuilder]$ cat verify.log.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name tf1
Database Version 10.2.0.1.0
Scan type Selective tables
Scan CHAR data? YES
Database character set WE8ISO8859P1
FROMCHAR WE8ISO8859P1
TOCHAR US7ASCII
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
[Application data individual exceptions]
[oracle@tf lbuilder]$
发现没有存在数据出现乱码的对象信息，说明导入和导出是在这两个不同字符集平台上数据在进行字符转换的时候是没有问题的。
另外txt文件是scan的过程信息，out是打在页面上的显示信息；
[oracle@tf lbuilder]$ cat verify.log.txt Database Scan Summary ReportTime Started : 2013-12-04 07:48:57Time Completed: 2013-12-04 07:49:17Process ID Time Started Time Completed---------- -------------------- -------------------- 1 2013-12-04 07:49:16 2013-12-04 07:49:16---------- -------------------- --------------------[Database Size]Tablespace Used Free Total Expansion------------------------- --------------- --------------- --------------- ---------------SYSTEM 473.81M 6.19M 480.00M .00KUNDOTBS1 2.94M 22.06M 25.00M .00KSYSAUX 232.69M 7.31M 240.00M .00KTEMP .00K .00K .00K .00KUSERS 448.00K 4.56M 5.00M .00KTEST 64.00K 19.94M 20.00M .00KTEMP02 .00K .00K .00K .00KRHYS 6.06M 3.94M 10.00M .00K------------------------- --------------- --------------- --------------- ---------------Total 716.00M 64.00M 780.00M .00K[Database Scan Parameters]Parameter Value ------------------------------ ------------------------------------------------CSSCAN Version v2.1 Instance Name tf1 Database Version 10.2.0.1.0 Scan type Selective tables Scan CHAR data? YES Database character set WE8ISO8859P1 FROMCHAR WE8ISO8859P1 TOCHAR US7ASCII Scan NCHAR data? NO Array fetch buffer size 1024000 Number of processes 10 Capture convertible data? NO ------------------------------ ------------------------------------------------[Scan Summary]All character type application data remain the same in the new character set[Data Dictionary Conversion Summary]Datatype Changeless Convertible Truncation Lossy--------------------- ---------------- ---------------- ---------------- ----------------VARCHAR2 0 0 0 0CHAR 0 0 0 0LONG 0 0 0 0CLOB 0 0 0 0VARRAY 0 0 0 0--------------------- ---------------- ---------------- ---------------- ----------------Total 0 0 0 0Total in percentage 0.000% 0.000% 0.000% 0.000%[Application Data Conversion Summary]Datatype Changeless Convertible Truncation Lossy--------------------- ---------------- ---------------- ---------------- ----------------VARCHAR2 28 0 0 0CHAR 0 0 0 0LONG 0 0 0 0CLOB 0 0 0 0VARRAY 0 0 0 0--------------------- ---------------- ---------------- ---------------- ----------------Total 28 0 0 0Total in percentage 100.000% 0.000% 0.000% 0.000%[Distribution of Convertible, Truncated and Lossy Data by Table]USER.TABLE Convertible Truncation Lossy-------------------------------------------------- ---------------- ---------------- ------------------------------------------------------------------ ---------------- ---------------- ----------------[Distribution of Convertible, Truncated and Lossy Data by Column]USER.TABLE|COLUMN Convertible Truncation Lossy-------------------------------------------------- ---------------- ---------------- ------------------------------------------------------------------ ---------------- ---------------- ----------------[Indexes to be Rebuilt]USER.INDEX on USER.TABLE(COLUMN) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------[oracle@tf lbuilder]$
That's all!
- 01-11全球最受赞誉公司揭晓：苹果连续九年第一
- 12-09罗伯特·莫里斯：让黑客真正变黑
- 12-09谁闯入了中国网络？揭秘美国绝密黑客小组TA
- 12-09警示：iOS6 惊现“闪退”BUG
- 05-15奇安信：数据安全问题是医疗卫生行业数字化
- 05-15腾讯宣布混元文生图大模型开源
- 05-15网易云音乐与韩国知名娱乐公司Kakao达成战略
- 05-15阿联酋宣告正式开启“5G-A全国商用”计划，
- 05-15二十年未遇的强烈地磁暴来袭
粤公网安备 44060402001498号