Oracle - SQLPlus中文顯示亂碼問題

襲冷發表於2018-01-17
OracleSQL
一、原因
    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



相關文章