一、原因
SqlPlus乱码与操作系统用户中的NLS_LANG这个环境变量有关系,如果这个与数据库字符集不一致的话就会产生乱码
SqlPlus乱码与操作系统用户中的NLS_LANG这个环境变量有关系,如果这个与数据库字符集不一致的话就会产生乱码
二、分析
查看系统环境变量NLS_LANGxl-db@oracle$echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
查看数据库字符集
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------
NLS_LANGUAGE AMERICAN -- 显示方式,就是 SQLPLUS 的程序的显示字体,有SIMPLIFIED CHINESE,AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8 -- 字符集设定, 常用的有UTF8,US7ASCII,WE8ISO8859P1,ZHS16GBK,AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION
查询NLS_LANG即操作系统环境变量要设为 NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET,如:
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
三、效果
#### 未修改NLS_LANG中字符集前的登录 ####
xl-db@oracle$echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
xl-db@oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 11 21:50:18 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
# 没有显示"连接到"的提示行
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
#### 修改为AMERICAN_AMERICA.AL32UTF8后 ####
xl-db@oracle$export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
xl-db@oracle$
xl-db@oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 21 21:51:14 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: # 有显示提示行
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
#### 修改为SIMPLIFIED CHINESE_CHINA.AL32UTF8后 ####
xl-db@oracle$export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
xl-db@oracle$
xl-db@oracle$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 11月 21 21:54:18 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到: # 有显示提示行
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options