Oracle - SQLPlus中文显示乱码问题

最新推荐文章于 2023-10-16 21:35:44 发布

袭冷

最新推荐文章于 2023-10-16 21:35:44 发布

阅读量1.3w

点赞数 5

分类专栏： DB 文章标签： Oracle SqlPlus 乱码

本文链接：https://blog.csdn.net/guoxilen/article/details/78612420

版权

DB 专栏收录该内容

52 篇文章 0 订阅

订阅专栏

一、原因
SqlPlus乱码与操作系统用户中的NLS_LANG这个环境变量有关系，如果这个与数据库字符集不一致的话就会产生乱码

二、分析

查看系统环境变量NLS_LANG

xl-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