radhat7.1
Oracle11gr2
安装Oracle时忘记设置字符集，导致安装后中文乱码，中文变成"???????????"
分析原因是Oracle服务器端和客户端的字符集不一致。
客户端字符集
[root@dbserver ~]# echo $NLS_LANG [root@dbserver ~]#
发现客户端字符集没有设置。
服务器端字符集
[oracle@dbserver ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Wen Jul 23 00:57:47 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started. Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 922749080 bytes
Database Buffers 654311424 bytes
Redo Buffers 7434240 bytes
Database mounted.
Database opened.
SQL> select userenv('language') from dual; USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN.AMERICA.AL32UTF8
//我的后边不是“AL32UTF8”，具体是哪一个忘了
SQL>
解决方法：
修改oracle服务器端编码
[oracle@dbserver ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 23 17:40:50 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started. Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 922749080 bytes
Database Buffers 654311424 bytes
Redo Buffers 7434240 bytes
Database mounted.
Database opened.
sql> conn / as sysdba;
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 135337420 bytes
fixed size 452044 bytes
variable size 109051904 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
sql> alter system enable restricted session;
system altered.
sql> alter system set job_queue_processes=0;
system altered.
sql> alter system set aq_tm_processes=0;
system altered.
sql> alter database open;
database altered.
sql> alter database character set internal_use ZHS16GBK;
sql> shutdown immediate;
sql> startup;
SQL> conn /as sysdba
Connected.
SQL> select userenv('language') from dual; USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbserver ~]$
修改oracle客户端编码
[oracle@dbserver ~]$ vim .bash_profile
在该文件中添加以下两行
NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export NLS_LANG
然后reboot系统。
注：修改用户名下的设置文件只对当前用户有效，如果修改/etc/目录下的文件对所有用户有效。
修改完成，查看结果
//客户端
[root@dbserver ~]# echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
[root@dbserver ~]#
//服务器端
[oracle@dbserver ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期四 7月 23 18:36:16 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved.
//看到中文的日期，说明客户端已经支持中文了
SQL> conn /as sysdba;
Connected to an idle instance.
SQL> startup;
ORACLE instance started. Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 922749080 bytes
Database Buffers 654311424 bytes
Redo Buffers 7434240 bytes
Database mounted.
Database opened.
SQL> conn mms/mms
Connected.
SQL> select * from wang; ID NAME
---------- --------------------
2 称国内
4 网横
1 ??
SQL>
//修改成功，此处最后一行仍为乱码，这是因为最开始添加数据的时候客户端与服务器端不一致的原因，因此最好新添加一条数据来测试。
SQL> select userenv('language') from dual; USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK SQL>
//此时结果跟最初只修改服务器端编码时的结果不一样，个人认为是受客户端编码的影响
SQL> exit
本文参考了网络中的多篇文档，具体网址忘记了