oracle字符集问题


字符集

====================
字符集在数据库创建的时候指定,在库建立以后一般都不会再更改

在创建数据库的时候,可以指定
character set    字符集
national character set    国家字符集

字符集的作用:
1、用来存放char、archar2、clob、long等类型数据
2、用来标识如表名、列名以及pl/sql变量等
3、用于存储sql和pl/sql代码等

国家字符集
用来存放nchar、nvarchar2、nclob等类型数据

-------------------

oracle的字符集命名通常遵循以下命名规则


ZHS 16 GBK

AL 16 UTF16

其中AL代表ALL,指适合所有的语言

-------------------

9i的字符文件缺省放在:
$ORACLE_HOME/ocommon/nls/admin/data

10g的字符文件缺省放在:
$ORACLE_HOME/nls/data

oracle提供了Local Builder工具来查看支持字符以及新建字符集

localchar值|字型|unicode值
oxb0b2|安|\u5b89

通过sqlplus工具查看汉字对应的aocalchar值可以按照以下步骤做

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

DUMP('安')                                                                     
---------------------                                                          
Typ=96 Len=2: 176,178                                                          

SQL> select concat(to_char(176,'xx'),to_char(178,'xx')) from dual;

CONCAT                                                                         
------                                                                         
 b0 b2                                                                         


以上步骤是可逆的:

SQL> select to_number('b0b2','xxxxxx') from dual;

TO_NUMBER('B0B2','XXXXXX')                                                     
--------------------------                                                     
                     45234                                                     

SQL> select chr(45234) from dual;

CH                                                                             
--                                                                             

--------------------

如果字符文件丢失,会导致sqlplus等工具不可用,数据库无法连接

我的字符集是默认的ZHS16GBK,用到的字符文件是lx00023.nlb
 
D:\oracle\product\10.2.0\db_1\nls\data>dir lx00023.nlb
 驱动器 D 中的卷没有标签。
 卷的序列号是 88CB-1FF7

 D:\oracle\product\10.2.0\db_1\nls\data 的目录

2005-08-17  14:16               791 lx00023.nlb
               1 个文件            791 字节
               0 个目录  6,711,402,496 可用字节
              
D:\oracle\product\10.2.0\db_1\nls\data>move lx00023.nlb lx00023.nlb.bak

D:\oracle\product\10.2.0\db_1\nls\data>sqlplus "/ as sysdba"
Error 5 initializing SQL*Plus
NLS initialization error

报错的原因是sqlplus启动的时候也需要调用字符文件

--------------------

修改字符集合

数据库创建以后,如果需要修改字符集,通常需要重建数据库,通过导入导出的方式转换
不过也可以通过下面的命令来进行修改

alter database character set

例:alter database character set ZHS16GBK


通过这种方法修改数据库的字符集,新的字符集一定要是原字符集的超集,不然会出现乱码
另外,要修改字符集,数据库一定要处在RESTRICTED状态
如果库中有clob类型字段,不允许对字符集合进行修改,可以通过alert日志报错来确定clob字段所在的表
先将有clob字段的表导出,删除表,再改字符集来实现目的


SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
第 1 行出现错误:
ORA-12719: 操作要求数据库处于 RESTRICTED 模式下

我的库本来的设置:
SQL> show parameter sql_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
sql_trace                            boolean     FALSE
SQL> show parameter job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
job_queue_processes                  integer     10
SQL> show parameter aq

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
aq_tm_processes                      integer     0


更改操作:
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             163580804 bytes
Database Buffers          440401920 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter session set sql_trace=true;

会话已更改。

SQL> alter system enable restricted session;

系统已更改。

SQL> alter system set job_queue_processes=0;

系统已更改。

SQL> alter system set aq_tm_processes=0;

系统已更改。

SQL> alter database open
  2  startup mount
  3
SQL> alter database open;

数据库已更改。

SQL> alter database character set ZHS16GBK;

数据库已更改。

这样就搞定了字符集的更改。

------------------------

识别导出文件的字符集

在windows使用ue或在unix使用cat查看导出文件的开头,可以看到

000000000h:03 03 54 45 58 50 4F.......
其中03 54代表导出文件字符集的

SQL> select to_number('354','xxx') from dual;

TO_NUMBER('354','XXX')
----------------------
                   852

SQL> select nls_charset_name(852) from dual;

NLS_CHAR
--------
ZHS16GBK

可以看到在windows中 03 54代表ZHS16GBK
而unix是 big-endians,windows和linux是little-endians,所以在
unix中
03 45才是ZHS16GBK

------------------------

客户端字符集的设置对查询的显示是有很大影响的

C:\Documents and Settings\xiang>set nls_lang=AMERICAN_AMERICA.ZHS16GBK

C:\Documents and Settings\xiang>sqlplus an/an

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 8 10:41:53 2008

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 current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
08-APR-08 10.42.08.703000 AM +08:00

SQL> select sysdate from dual;

SYSDATE
------------
08-APR-08


以上显示为英文

C:\Documents and Settings\xiang>set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

C:\Documents and Settings\xiang>sqlplus an/an

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 4月 8 10:57:03 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
08-4月 -08 10.57.14.125000 上午 +08:00

SQL> select sysdate from dual;

SYSDATE
--------------
08-4月 -08

显示为中文

------------------------

csscan的使用

csscan工具能扫描数据库,找到所有不兼容的字符

在使用csscan之前需要以sys用户身份创建响应数据字典对象

以sysdba身份登陆

C:\Documents and Settings\xiang>sqlplus an/an as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 4月 8 16:00:44 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\csminst.sql

运行脚本,创建相应的数据字典对象

C:\>csscan an/an full=y fromchar=ZHS16GBK tochar=US7ASCII log=e:\csscan.log capture=y array=1000000 process=2

通过查看日志,可以发现不兼容的字符

log内容

Database Scan Summary Report

Time Started  : 2008-04-08 15:59:29
Time Completed: 2008-04-08 16:01:25

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2008-04-08 15:59:49  2008-04-08 16:01:24
         2  2008-04-08 15:59:49  2008-04-08 16:01:24
---------- -------------------- --------------------

[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            259.44M          40.56M         300.00M            .00K
UNDOTBS1                           81.31M         118.69M         200.00M            .00K
SYSAUX                            102.56M          17.44M         120.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                              64.00K           4.94M           5.00M            .00K
AN                                256.00K         199.75M         200.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                             443.63M         381.38M         825.00M            .00K

The size of the largest CLOB is 351364 bytes

[Database Scan Parameters]

Parameter                      Value                                          
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                           
Instance Name                  an10                                           
Database Version               10.2.0.1.0                                     
Scan type                      Full database                                  
Scan CHAR data?                YES                                            
Database character set         ZHS16GBK                                       
FROMCHAR                       ZHS16GBK                                       
TOCHAR                         US7ASCII                                       
Scan NCHAR data?               NO                                             
Array fetch buffer size        1000000                                        
Number of processes            2                                              
Capture convertible data?      NO                                             
------------------------------ ------------------------------------------------

[Scan Summary]

Some character type data in the data dictionary are not convertible to the new character set
Some character type application data are not convertible to the new character set

[Data Dictionary Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     1,121,944                0                0                5
CHAR                                 0                0                0                0
LONG                           108,634                0                0                0
CLOB                               823              505                0                0
VARRAY                              27                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        1,231,428              505                0                5
Total in percentage             99.959%           0.041%           0.000%           0.000%

The data dictionary can not be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                        30,810                0                0               38
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
CLOB                                 0                0                0                0
VARRAY                           1,436                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                           32,246                0                0               38
Total in percentage             99.882%           0.000%           0.000%           0.118%

[Distribution of Convertible, Truncated and Lossy Data by Table]

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
AN.AN                                                             0                0                1
AN.AN_BAK                                                         0                0                1
SYS.JOB$                                                          0                0                1
SYS.METASTYLESHEET                                               80                0                0
SYS.RULE$                                                         4                0                0
SYS.SCHEDULER$_EVENT_LOG                                          4                0                0
SYS.SCHEDULER$_JOB                                                0                0                4
SYS.WRH$_SQLTEXT                                                174                0                0
SYS.WRH$_SQL_PLAN                                               134                0                0
SYS.WRI$_DBU_FEATURE_METADATA                                    90                0                0
SYS.WRI$_DBU_FEATURE_USAGE                                        2                0                0
SYS.WRI$_DBU_HWM_METADATA                                        17                0                0
SYSMAN.MGMT_STRING_METRIC_HISTORY                                 0                0               36
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
AN.AN|ID                                                          0                0                1
AN.AN_BAK|ID                                                      0                0                1
SYS.JOB$|NLSENV                                                   0                0                1
SYS.METASTYLESHEET|STYLESHEET                                    80                0                0
SYS.RULE$|CONDITION                                               4                0                0
SYS.SCHEDULER$_EVENT_LOG|ADDITIONAL_INFO                          4                0                0
SYS.SCHEDULER$_JOB|NLS_ENV                                        0                0                4
SYS.WRH$_SQLTEXT|SQL_TEXT                                       174                0                0
SYS.WRH$_SQL_PLAN|OTHER_XML                                     134                0                0
SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC                     11                0                0
SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC                      79                0                0
SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO                           2                0                0
SYS.WRI$_DBU_HWM_METADATA|LOGIC                                  17                0                0
SYSMAN.MGMT_STRING_METRIC_HISTORY|STRING_VALUE                    0                0               36
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)                                                        
-----------------------------------------------------------------------------------------
SYS.I_SCHEDULER_JOB4 on SYS.SCHEDULER$_JOB(SYS_NC00044$)
SYS.I_SCHEDULER_JOB1 on SYS.SCHEDULER$_JOB(SYS_NC00043$)
-----------------------------------------------------------------------------------------

乱码的产生

乱码的产生涉及到3个字符集

客户端应用字符集
客户端nls_lang参数设置
服务器数据库字符集设置

如果不指定nls_lang,系统直接取客户端系统字符集
如果客户端系统字符集与服务器数据库字符集不同就需要设置nls_lang参数来指定使用的字符集


我的库默认的字符集是ZHS16GBK,通过以下设置产生了乱码


C:\Documents and Settings\xiang>set nls_lang=AMERICAN_AMERICA.US7ASCII

C:\Documents and Settings\xiang>sqlplus an/an

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 8 17:20:24 2008

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 * from an;

ID
--------------------
111
aaa
SDF
???


设置相同的nls_lang环境变量参数

C:\Documents and Settings\xiang>set nls_lang=american_america.ZHS16GBK

C:\Documents and Settings\xiang>sqlplus an/an

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 8 17:08:26 2008

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 * from an;

ID
--------------------
111
aaa
SDF
安佰胜

这样就避免了乱码的产生


----------------------

‘靠’字的产生

设置nls_lang

C:\>set nls_lang=SIMPLIFIED CHINESE_CHINA.WE8MSWIN1252


执行导出命令
C:\Documents and Settings\xiang>exp an/an file=e:\111.dmp

Export: Release 10.2.0.1.0 - Production on 靠?4?8 11:07:50 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


EXP-00056: 靠 ORACLE 靠 12560
ORA-12560: TNS: 靠靠靠
EXP-00000: 靠靠靠

C:\Documents and Settings\xiang>set oracle_sid=an10

C:\Documents and Settings\xiang>exp an/an file=e:\111.dmp

Export: Release 10.2.0.1.0 - Production on 靠?4?8 11:26:14 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


靠? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
靠?WE8MSWIN1252 靠靠 AL16UTF16 NCHAR 靠
靠靠?ZHS16GBK 靠?(靠靠靠靠)

靠靠靠靠?..
. 靠靠 pre-schema 靠靠靠
. 靠靠靠 AN 靠靠靠
. 靠 PUBLIC 靠靠
. 靠靠靠靠靠
. 靠靠靠 AN 靠靠靠
靠靠 AN 靠?..
. 靠靠靠靠
. 靠靠靠
. 靠靠靠
. 靠靠 AN 靠靠靠靠...
. . 靠靠?                             AN靠?        100
. . 靠靠?                           TEST靠?          1
. 靠靠靠
. 靠靠靠
. 靠靠靠靠
. 靠靠靠
. 靠靠靠靠靠靠
. 靠靠靠
. 靠靠靠靠
. 靠靠靠, 靠靠靠靠靠
. 靠靠靠靠
. 靠靠靠靠
. 靠靠靠靠
. 靠靠靠靠
. 靠靠靠靠靠
. 靠靠
. 靠靠 post-schema 靠靠靠
. 靠靠靠靠
靠靠靠, 靠靠靠?

出现了若干的靠字,很郁闷

右键cmd窗口标志条,选择属性可以看到该窗口的采用的代码
我的是GBK 
因为设置了set nls_lang=SIMPLIFIED CHINESE_CHINA.WE8MSWIN1252,所以返回信息需要转码

WE8MSWIN1252是单字节编码,转换成GBK回出问题,认不到的都转成了倒的‘?’,这个字符的代码是BFBF
BFBF在gbk中刚好就是‘靠’字,所以会出现若干的靠


-----------------------------

csscan 扫描


正式修改字符集前,我已经使用csscan 1.2 扫描两边整个库了
第一次fromchar=gbk 和 tochar=gbk
第二次fromchar=231280 和 tochar=gbk
第一次的csm$errors 表纪录比第二次多得多
我相信第二次的结果准确些,但还是有不少csm$errors 纪录
无法一一验证
当时也不理会,直接
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK
成功,过后总是记挂着 gbk 不是231280的超集,是否会有231280 中的汉字到gbk 中不能显示
您是否碰到这种汉字?

-------------------------------

修改NLS_NCHAR_CHARACTERSET

SHUTDOWN IMMEDIATE                                       
STARTUP MOUNT                                            
ALTER SYSTEM ENABLE RESTRICTED SESSION;                  
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;                  
ALTER SYSTEM SET AQ_TM_PROCESSES=0;                      
ALTER DATABASE OPEN                                      
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8; 
SHUTDOWN IMMEDIATE                                       
STARTUP                                                  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-674107/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/13177610/viewspace-674107/

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值