oracle 服务端修改字符集

客户端配置NLS_LANG说明

如果发现你select 出来的数据是乱码，请把client端的NSL_LANG配置成与linux操作系统相同的字符集。如果还是有乱码，则有可能是数据库中的数据存在问题，或者是oracle服务端的配置存在问题。

例如：

[oracle@i-0be14e11 ~]$ locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=

在客户端中查询到 LANG=zh_CN.UTF-8,则客户端的NSL_LANG应配置为SIMPLIFIED CHINESE_CHINA.AL32UTF8

NSL_LANG不是设置客户端的字符集，而是告诉oracle服务端客户端使用什么字符集，oracle服务端返回给客户端的字符集为客户端设置的NSL_LANG,oracle服务端会自动转码。所以只要保证客户端设置的NSL_LANG与客户端的编码相对应，一般就没有那么多奇奇怪怪乱码的问题。

操作系统locale 对应的 NLS_LANG

Language	Locale ID	NLS_LANG
English (American)	en_US.UTF-8	AMERICAN_AMERICA.AL32UTF8
English (American)	en_US.ISO-8859-1	AMERICAN_AMERICA.WE8ISO8859P1
English (American)	en_US.ISO-8859-15	AMERICAN_AMERICA.WE8ISO8859P15
English (Australian)	en_AU.UTF-8	ENGLISH_AUSTRALIA.AL32UTF8
English (Australian)	en_AU.ISO-8859-1	ENGLISH_AUSTRALIA.WE8ISO8859P1
English (Australian)	en_AU.ISO-8859-15	ENGLISH_AUSTRALIA.WE8ISO8859P15
English (British)	en_GB.UTF-8	ENGLISH_UNITED KINGDOM.AL32UTF8
English (British)	en_GB.ISO-8859-1	ENGLISH_UNITED KINGDOM.WE8ISO8859P1
English (British)	en_GB.ISO-8859-15	ENGLISH_UNITED KINGDOM.WE8ISO8859P15
English (Ireland)	en_IE.UTF-8	ENGLISH_IRELAND.AL32UTF8
English (Ireland)	en_IE.ISO-8859-1	ENGLISH_IRELAND.WE8ISO8859P1
English (Ireland)	en_IE.ISO-8859-15	ENGLISH_IRELAND.WE8ISO8859P15
German	de_DE.UTF-8	GERMAN_GERMANY.AL32UTF8
German	de_DE.ISO-8859-1	GERMAN_GERMANY.WE8ISO8859P1
German	de_DE.ISO-8859-15	GERMAN_GERMANY.WE8ISO8859P15
French	fr_FR.UTF-8	FRENCH_FRANCE.AL32UTF8
French	fr_FR.ISO-8859-1	FRENCH_FRANCE.WE8ISO8859P1
French	fr_FR.ISO-8859-15	FRENCH_FRANCE.WE8ISO8859P15
Italian	it_IT.UTF-8	ITALIAN_ITALY.AL32UTF8
Italian	it_IT.ISO-8859-1	ITALIAN_ITALY.WE8ISO8859P1
Italian	it_IT.ISO-8859-15	ITALIAN_ITALY.WE8ISO8859P15
Spanish	es_ES.UTF-8	SPANISH_SPAIN.AL32UTF8
Spanish	es_ES.ISO-8859-1	SPANISH_SPAIN.WE8ISO8859P1
Spanish	es_ES.ISO-8859-15	SPANISH_SPAIN.WE8ISO8859P15
Spanish (Mexico)	es_MX.UTF-8	MEXICAN SPANISH_MEXICO.AL32UTF8
Spanish (Mexico)	es_MX.ISO-8859-1	MEXICAN SPANISH_MEXICO.WE8ISO8859P1
Spanish (Mexico)	es_MX.ISO-8859-15	MEXICAN SPANISH_MEXICO.WE8ISO8859P15
Portuguese (Brazilian)	pt_BR.UTF-8	BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8
Portuguese (Brazilian)	pt_BR.ISO-8859-1	BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1
Portuguese (Brazilian)	pt_BR.ISO-8859-15	BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P15
Japanese	ja_JP.EUC-JP	JAPANESE_JAPAN.JA16EUC
Japanese	ja_JP.UTF-8	JAPANESE_JAPAN.AL32UTF8
Korean	ko_KR.EUC-KR	KOREAN_KOREA.KO16KSC5601
Korean	ko_KR.UTF-8	KOREAN_KOREA.AL32UTF8
Chinese (simplified)	zh_CN.GB18030	SIMPLIFIED CHINESE_CHINA.ZHS32GB18030
Chinese (simplified)	zh_CN.UTF-8	SIMPLIFIED CHINESE_CHINA.AL32UTF8
Chinese (traditional)	zh_TW.BIG5	TRADITIONAL CHINESE_*.ZHT16BIG5
Chinese (traditional)	zh_TW.UTF-8	TRADITIONAL CHINESE_*.AL32UTF8

 

server端字符集修改

更改字符集步骤方法(WE8ISO8859P1 --> ZHS16GBK)

开始 - 运行 - 输入cmd 进入命令提示符

-- 以管理员身份登录sqlplus
sqlplus / as sysdba;

-- 关闭数据库
shutdown immediate;

-- 将数据库启动到mout模式
startup mount

-- 数据库受限模式，在这个模式下只有RESTRICTED SESSION 权限的人才可以登陆，一般用与数据库维护的时候使用。
ALTER SYSTEM ENABLE RESTRICTED SESSION;
-- 让JOB没有调度进程，不会run。防止有任务自动启动执行 （可以不设置）
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
-- 关闭队列监视器（可以不设置）
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

-- 将数据库启动到open模式
alter database open;

-- 修改字符集（新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改）
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

-- 查看字符集
select * from v$nls_parameters;

-- 重启再查看
shutdown immediate;
startup

-- 修改客户端的环境变量
-- linux下修改环境变量（临时）
-- export NLS_LANG=“SIMPLIFIED Chinese_CHINA.ZHS16GBK”
-- 如果是windows，按照以下方法添加或者修改，在系统变量中添加如下信息：
-- 变量名：NLS_LANG
-- 变量值：SIMPLIFIED Chinese_CHINA.ZHS16GBK

 

本文地址：https://blog.csdn.net/qq_41360111/article/details/110622015