Oracle 字符集常见字符集及解决方案

优先级别：alter session>环境变量>注册表>参数文件

一.查看字符集：

1.查询服务端字符集：

select userenv(‘language‘) from dual

or

select * from nls_database_parameters

2.查询客户端：

select * from nls_instance_parameters

客户端字符集与服务端字符集不一致就会出现乱码等问题

 

3.会话字符集：

select * from nls_session_parameters

二.修改字符集：

1.修改客户端字符集：（推荐）

打开注册表：cmd-regedit

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1的NLS_LANG

修改NLS_LANG的值为：

比如服务端是：SIMPLIFIED CHINESE_CHINA.AL32UTF8那么客户端也修改成它即可

如果通过注册表方式还是没有解决问题，去修改环境变量：NLS_LANG

dos命令：set nls_lang=AMERICAN_AMERICA.ZHS16GBK

2.服务器端字符集修改：（慎重）

 

三.常用字符集：

ZHS16GBK

ZHS16CGB231280

AL32UTF8     9i出现的

语法：国家_时间格式.字符集格式

AMERICAN_AMERICA.ZHS16GBK

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SIMPLIFIED CHINESE_CHINA.AL32UTF8

四.字符集表：

 

 

=================================================

一、查看字符集

C:\Users\Admin>sqlplus/nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 19 15:30:52 2018

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

SQL> conn /as sysdba;
已连接。
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select userenv(‘language‘) from dual;

USERENV(‘LANGUAGE‘)
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SQL> select * from v$nls_parameters;

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
￥


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE

NLS_CHARACTERSET
ZHS16GBK


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
￥


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE


已选择19行。

SQL> select * from nls_database_parameters;

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
ZHS16GBK


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE


PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
11.2.0.1.0


已选择20行。