Oracle客户端toad汉字乱码解决方法

应一直受服务端编码和客户端编码要一致误导，结果害了我半个小时时间，来解决这个小问题，现在把解决记录一下，以备忘记：

出现中文乱码的主要原因是字符集不同。在Oracle中，我们关心三个地方的字符集：

先来查看一下数据库字符集：
在Oracle中可能使用Select userenv('language') from dual;或者：Select name, value$ from props$;查看。

l Oracle服务器内部的字符集
修改方法：
connect sys/chang_on_install
update props$　set value$='TRADITIONAL CHINESE_TAIWAN.AL32UTF8'where name='NLS_CHARACTERSET';
commit;

2 NLS_LANG变量里保存的字符集

这个是Oracle设置的一个变量。在Windows中，这个变量保存在注册表中：
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 保存着NLS_LANG变量。

在Unix/Linux中，则需要自己进行设置，在.profile里面加上 NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG

3 客户端应用的字符集

下面是我用到的字符集
american_america.AL32UTF8
TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
TRADITIONAL CHINESE_TAIWAN.AL32UTF8
AMERICAN_AMERICA.WE8ISO8859P1
AMERICAN_AMERICA.ZHS16GBK

特别注意：如果服务端编码为AL32UTF8，那么客户端就应该安装自己环境来设置字符编码。
比如我有一个服务器端，其中两个客户端一个为简体，一个为繁体：
服务器端字符集设置：american_america.AL32UTF8
简体客户端字符集设置：AMERICAN_AMERICA.ZHS16GBK
繁体客户端字符集设置：TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950

这样toad和sqlplus不论在繁体还是简体都可以正常显示汉字了。

评论

2 楼 seekweel 2011-05-26  

ORA-12638: Credential retrieval failed

修改：
# SQLNET.ORA Network Configuration File: X:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DEFAULT_DOMAIN = GDS.COM

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES)
原因：
--------------
I'm no Oracle expert, but from what can make out the NTS option makes the Oracle client attempt to use your current Windows domain credentials to authenticate you with the Oracle server. This could fail for a couple of reasons:

- The Oracle server is not configured to support Windows authentication
- The credentials you use to login to your local machine are not sufficient to allow you to login to the server.

In my case, it was the later. Despite the fact that I had told the client to use a different user name and password, it was still attempting to login using my domain credentials first. This failed because I was logged on to my local machine using my normal domain credentials rather than my administrator account.

Replacing the line:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

with

SQLNET.AUTHENTICATION_SERVICES= (NONE)

in sqlnet.ora resolved the issue by disabling local support for authenticating using Windows credentials.

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

1 楼 zuoguodang 2011-04-26  

2年前就遇到过，windows下设置一下客户端的环境变量就可以了