产生这个问题的原因:

很多人安装linux时喜欢选择西文系统,这是习惯问题,有些下载版本的linux 缺省安装也是西文系统,通常西文系统并不影响部署在上面的中文应用的运行.

但在西文linux下安装oracle的时候,oracle installer会自动根据当前系统选择编码,而你安装的时候如果不定制,那装出来的就是西文编码,NLS_CHARACTERSET=WE8ISO8859P,而我们需要的是NLS_CHARACTERSET=ZHS16GBK,ZHS16GBK是中文简体

这样的后果,就是装出来的oracle不支持中文库表和中文记录数据,全是乱码


查看oracle 当前字符集

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';      PARAMETER                                                    VALUE ------------------------------------------------------------ ----------------------- NLS_CHARACTERSET                                             WE8ISO8859P1

常用的一些字符集，如 US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK, AL32UTF8


修改linux当前编码(option)
虽然这个步骤不是必须的,但还是建议做:

#cat /etc/sysconf/i18n 如果你看到如下,则说明你的linux是西文系统; LANG="en_US.UTF-8"

改成如下,重启系统,你的linux就是中文系统了 LANG="zh_CN"

如果你看到如下几种配置,都说明你的系统是中文系统,不需要更改
LANG="zh_CN"
LANG="zh_CN.UTF-8"
LANG="zh_CN.GBK"
LANG="zh_CN.GB2312"
LANG="zh_CN.GB18030"


为oracle用户加一个环境变量

[oracle@oracle bin]$ cd /home/oracle [oracle@oracle ~]$ cat .bash_profile # .bash_profile umask 022 # Get the aliases and functions if [ -f ~/.bashrc ]; then         . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH unset USERNAME export ORACLE_BASE=/u01/app/oracle export ORACLE_SID=orcl export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1/ export ORACLE_HOME_LISTNER=$ORACLE_HOME export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"         加此句 export PATH=$PATH:$ORACLE_HOME/bin

以mount 模式启动oracle 系统--------这种启动模式通常用于维护,备份,恢复

[oracle@oracle ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 4 13:35:54 2010 Copyright (c) 1982, 2005, Oracle.  All rights reserved. SQL> conn /as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount    ORACLE instance started. Total System Global Area  285212672 bytes Fixed Size                  1218968 bytes Variable Size             117442152 bytes Database Buffers          159383552 bytes Redo Buffers                7168000 bytes Database mounted. SQL>

开始改字符集

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 ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-12712: new character set must be a superset of old character set 提示我们的字符集：新字符集必须为旧字符集的超集

我们可以跳过超集的检查做更改： SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; Database altered.

看结果

SQL> set linesize 300 SQL> select * from v$nls_parameters; PARAMETER                                                        VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- NLS_LANGUAGE                                                     SIMPLIFIED CHINESE NLS_TERRITORY                                                    CHINA NLS_CURRENCY                                                     ？ NLS_ISO_CURRENCY                                                 CHINA NLS_NUMERIC_CHARACTERS                                           ., NLS_CALENDAR                                                     GREGORIAN NLS_DATE_FORMAT                                                  DD-MON-RR NLS_DATE_LANGUAGE                                        SIMPLIFIED CHINESE NLS_CHARACTERSET                                         ZHS16GBK NLS_SORT                                                         BINARY NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY                                                ？ NLS_NCHAR_CHARACTERSET                                           AL16UTF16 NLS_COMP                                                         BINARY NLS_LENGTH_SEMANTICS                                             BYTE NLS_NCHAR_CONV_EXCP                                              FALSE

系统恢复,重新启动

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area  285212672 bytes Fixed Size                  1218968 bytes Variable Size             117442152 bytes Database Buffers          159383552 bytes Redo Buffers                7168000 bytes Database mounted. Database opened. SQL>