oracle字符集相關整理

整理自網路+實驗

字符集介紹

影響Oracle資料庫字符集最重要的引數是NLS_LANG引數。
它的格式如下: NLS_LANG = language_territory.charset
 NLS_LANG引數由以下部分組成:
NLS_LANG=<Language>_<Territory>.<Clients Characterset>
NLS_LANG各部分含義如下:
LANGUAGE指定:
-Oracle訊息使用的語言
-日期中月份和日顯示
TERRITORY指定
-貨幣和數字格式
-地區和計算星期及日期的習慣

 

字符集
    實質就是按照一定的字元編碼方案，對一組特定的符號，分別賦予不同數值編碼的集合。Oracle資料庫最早支援的編碼方案是US7ASCII。
    Oracle的字符集命名遵循以下命名規則:
    <Language><bit size><encoding>
    即: <語言><位元位數><編碼>
    比如: ZHS16GBK表示採用GBK編碼格式、16位（兩個位元組）簡體中文字符集

如何查詢資料庫字符集 

 

EXP/IMP 與 字符集
EXP/IMP
    Export 和Import 是一對讀寫Oracle資料的工具。Export 將Oracle 資料庫中的資料輸出到作業系統檔案中, Import 把這些檔案中的資料讀到Oracle 資料庫中，由於使用exp/imp進行資料遷移時，資料從源資料庫到目標資料庫的過程中有四個環節涉及到字符集，如果這四個環節的字符集不一致，將會發生字符集轉換。
EXP
     ____________ _________________ _____________
     |imp匯入檔案|<-|環境變數NLS_LANG|<-|資料庫字符集|
      ------------   -----------------   -------------
IMP
     ____________ _________________ _____________
     |imp匯入檔案|->|環境變數NLS_LANG|->|資料庫字符集|
      ------------   -----------------   -------------


四個字符集是
   （1）源資料庫字符集
   （2）Export過程中使用者會話字符集（透過NLS_LANG設定）
   （3）Import過程中使用者會話字符集（透過NLS_LANG設定）
   （4）目標資料庫字符集

5.2匯出的轉換過程
    在Export過程中，如果源資料庫字符集與Export使用者會話字符集不一致，會發生字符集轉換，並在匯出檔案的頭部幾個位元組中儲存Export使用者會話字符集的ID號。在這個轉換過程中可能發生資料的丟失。

例:如果源資料庫使用ZHS16GBK，而Export使用者會話字符集使用US7ASCII，由於ZHS16GBK是16位字符集,而US7ASCII是7位字符集，這個轉換過程中，中文字元在US7ASCII中不能夠找到對等的字元，所以所有中文字元都會丟失而變成“?? ”形式，這樣轉換後生成的Dmp檔案已經發生了資料丟失。
因此如果想正確匯出源資料庫資料，則Export過程中使用者會話字符集應等於源資料庫字符集或是源資料庫字符集的超集

5.3匯入的轉換過程
    （1）確定匯出資料庫字符集環境
             透過讀取匯出檔案頭，可以獲得匯出檔案的字符集設定
    （2）確定匯入session的字符集，即匯入Session使用的NLS_LANG環境變數
    （3）IMP讀取匯出檔案
             讀取匯出檔案字符集ID，和匯入程式的NLS_LANG進行比較
    （4）如果匯出檔案字符集和匯入Session字符集相同，那麼在這一步驟內就不需要轉換，             如果不同，就需要把資料轉換為匯入Session使用的字符集。可以看出，匯入資料到資料庫過程中發生兩次字符集轉換

    第一次:匯入檔案字符集與匯入Session使用的字符集之間的轉換，如果這個轉換過程不能正確完成，Import向目標資料庫的匯入過程也就不能完成。
    第二次:匯入Session字符集與資料庫字符集之間的轉換。

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

 

 

dmp檔案字符集檢視(EXP)

 

SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;

NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX'))
--------------------------------------------------------------------------------
ZHS16GBK

 

如果dmp檔案很大，比如有2G以上(這也是最常見的情況)，用文字編輯器開啟很慢或者完全打不開，可以用以下命令(在unix主機上):

 

[root@NETDATA11G-01 ~]#.cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
0345

 

查詢oracle client端的字符集
在windows平臺下，就是登錄檔裡面相應OracleHome的NLS_LANG。還可以在dos視窗裡面自己設定，
比如: set nls_lang=AMERICAN_AMERICA.ZHS16GBK

一般出現最多問題就是plsqldevelop問題

在sqlplus 建立一個指令碼

@echo off

 set nls_lang=資料庫端字符集

start plsqldevelop.exe

 

linux 客戶端建議在環境變數裡面設定

NLS_LANG=American_America.zhs16gbk;export NLS_LANG

 

查詢資料庫字符集

SQL> set line 200
SQL> col VALUE format A50
SQL> col parameters format A20
SQL> select * from nls_database_parameters;

PARAMETER                 VALUE
------------------------------ --------------------------------------------------
NLS_LANGUAGE                 AMERICAN
NLS_TERRITORY                 AMERICA
NLS_CURRENCY                 $
NLS_ISO_CURRENCY            AMERICA
NLS_NUMERIC_CHARACTERS            .,
NLS_CHARACTERSET            ZHS16GBK
NLS_CALENDAR                 GREGORIAN
NLS_DATE_FORMAT             DD-MON-RR
NLS_DATE_LANGUAGE            AMERICAN
NLS_SORT                 BINARY
NLS_TIME_FORMAT             HH.MI.SSXFF AM

PARAMETER                 VALUE
------------------------------ --------------------------------------------------
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            11.2.0.4.0

20 rows selected.

 

SQL> select '測試' from dual;

'測
------
測試

SQL>

 

 

 

常見問題

imp匯入亂碼

修改dmp檔案字符集
上文說過，dmp檔案的第2第3位元組記錄了字符集資訊，因此直接修改dmp檔案的第2第3位元組的內容就可以‘騙’過oracle的檢查。這樣做理論上也僅是從子集到超集可以修改，但很多情況下在沒有子集和超集關係的情況下也可以修改，我們常用的一些字符集，如US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因為改的只是dmp檔案，所以影響不大。

 

總結

修改NLS引數
    使用下列方法可以修改NLS引數
    （1）修改例項啟動時使用的初始化引數檔案
    （2）修改環境變數NLS_LANG
    （3）使用ALTER SESSION語句，在oracle會話中修改
    （4）使用某些SQL函式

客戶端的字符集要求與伺服器一致，才能正確顯示資料庫的非Ascii字元。
如果多個設定存在的時候，NLS作用優先順序別：Sql function > alter session > 環境變數或登錄檔> 引數檔案> 資料庫預設引數

 

修改伺服器端字符集

 資料庫字符集在建立後原則上不能更改。因此，在設計和安裝之初考慮使用哪一種字符集十分重要。對資料庫server而言，錯誤的修改字符集將會導致很多不 可測的後果，可能會嚴重影響資料庫的正常執行，所以在修改之前一定要確認兩種字符集是否存在子集和超集的關係。一般來說，除非萬不得已，我們不建議修改 oracle資料庫server端的字符集。特別說明，我們最常用的兩種字符集ZHS16GBK和ZHS16CGB231280之間不存在子集和超集關 系，因此理論上講這兩種字符集之間的相互轉換不受支援。

 不過修改字符集有2種方法可行。
1. 通常需要匯出資料庫資料，重建資料庫，再匯入資料庫資料的方式來轉換。
2. 透過ALTER DATABASE CHARACTER SET語句修改字符集，但建立資料庫後修改字符集是有限制的，只有新的字符集是當前字符集的超集時才能修改資料庫字符集，例如UTF8是US7ASCII的超集，修改資料庫字符集可使用ALTER DATABASE CHARACTER SET UTF8。

啟動到Mount
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE OPEN;
--這裡可以從父集到子集
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
SQL>ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16;
--如果是從子集到父集，需要使用INTERNAL_USE 引數，跳過超子集檢測
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;