oracle字符集深入研究

fvgb

   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                                 Language  
NLS_TERRITORY                  AMERICA                                  Territory  
NLS_CURRENCY                   $                                        Local currency  
NLS_ISO_CURRENCY               AMERICA                                  ISO currency  
NLS_NUMERIC_CHARACTERS         .,                                       Numeric characters  
NLS_CHARACTERSET               WE8ISO8859P1                             Character set  
NLS_CALENDAR                   GREGORIAN                                Calendar system  
NLS_DATE_FORMAT                DD-MON-RR                                Date format  
NLS_DATE_LANGUAGE              AMERICAN                                 Date language  
NLS_SORT                       BINARY                                   Linguistic definition  

NAME                           VALUE$                                   COMMENT$  
------------------------------ ---------------------------------------- ----------------------------------------------------------------------  
NLS_TIME_FORMAT                HH.MI.SSXFF AM                           Time format  
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                 Time stamp format  
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                       Time with timezone format  
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR             Timestamp with timezone format  
NLS_DUAL_CURRENCY              $                                        Dual currency symbol  
NLS_COMP                       BINARY                                   NLS comparison  
NLS_LENGTH_SEMANTICS           BYTE                                     NLS length semantics  
NLS_NCHAR_CONV_EXCP            FALSE                                    NLS conversion exception  
NLS_NCHAR_CHARACTERSET         AL16UTF16                                NCHAR Character set  
NLS_RDBMS_VERSION              10.2.0.1.0                               RDBMS version for NLS parameters  

21 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')  
------------------------------  
                            31  

SQL> select dump('rhys') from dual;  

DUMP('RHYS')  
-----------------------------  
Typ=96 Len=4: 114,104,121,115  

SQL> select to_char('114','xxxxx') from dual;  

TO_CHA  
------  
    72  

SQL> select to_number('72','xxxxx') from dual;  

TO_NUMBER('72','XXXXX')  
-----------------------  
                    114  

SQL> select chr(114) from dual;  

C  
-  
r  

SQL> select to_char('104','xxxx' ) from dual;  

TO_CH  
-----  
   68  

SQL> 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.<span style="color:#ff0000;">US7ASCII  
</span>[oracle@tf lbuilder]$ exp system/root file=us.dmp owner=scott rows=y  

Export: Release 10.2.0.1.0 - Production on Wed Dec 4 07:16:33 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  
Export done in<span style="color:#ff0000;"> US7ASCII </span>character set and AL16UTF16 NCHAR character set  
server uses <span style="color:#ff0000;">WE8ISO8859P1</span> character set (<span style="color:#ff0000;">possible charset conversion</span>)  

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 sysdba  

SQL*Plus: Release 10.2.0.1.0 - Production on ?l? 12? 4 07:45:04 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> @?/rdbms/admin/csminst.sql  
grant READ on directory log_file_dir to system  
                        *  
ERROR at line 1:  
ORA-22930: directory does not exist  


grant READ on directory data_file_dir to system  
                        *  
ERROR at line 1:  
ORA-22930: directory does not exist  


drop user csmig cascade  
          *  
ERROR at line 1:  
ORA-01918: user 'CSMIG' does not exist  


Please create password for user CSMIG:  
Enter value for csmig_passwd: root  
old   1: create user csmig identified by &csmig_passwd  
new   1: create user csmig identified by root  

User 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 - Production  
With the Partitioning, OLAP and Data Mining options  
[oracle@tf lbuilder]$   

之后使用csscan工具进行校验，如校验一张表在数据移动时是否存在问题。
[oracle@tf lbuilder]$ <span style="color:#ff0000;">csscan system/root table=scott.emp fromchar=WE8ISO8859P1 tochar=US7ASCII log=verify.log array=1024000  </span>process=10  


Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on ?l? 12? 4 07:48:57 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  

Enumerating 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 Report  

Time Started  : 2013-12-04 07:48:57  
Time Completed: 2013-12-04 07:49:17  

Process 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            .00K  
UNDOTBS1                            2.94M          22.06M          25.00M            .00K  
SYSAUX                            232.69M           7.31M         240.00M            .00K  
TEMP                                 .00K            .00K            .00K            .00K  
USERS                             448.00K           4.56M           5.00M            .00K  
TEST                               64.00K          19.94M          20.00M            .00K  
TEMP02                               .00K            .00K            .00K            .00K  
RHYS                                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                0  
CHAR                                 0                0                0                0  
LONG                                 0                0                0                0  
CLOB                                 0                0                0                0  
VARRAY                               0                0                0                0  
--------------------- ---------------- ---------------- ---------------- ----------------  
Total                                0                0                0                0  
Total in percentage              0.000%           0.000%           0.000%           0.000%  


[Application Data Conversion Summary]  

Datatype                    Changeless      Convertible       Truncation            Lossy  
--------------------- ---------------- ---------------- ---------------- ----------------  
VARCHAR2                            28                0                0                0  
CHAR                                 0                0                0                0  
LONG                                 0                0                0                0  
CLOB                                 0                0                0                0  
VARRAY                               0                0                0                0  
--------------------- ---------------- ---------------- ---------------- ----------------  
Total                               28                0                0                0  
Total 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!

janneyabc

看着别人的故事，留着自己的眼泪。