Oracle 字符集深度解析：NLS_LANG 設定、查詢、修改與亂碼根治

字符集三角關係：Server、Client、Dump File

亂碼本質是三端字符集不一致導致的錯誤轉換：

• Server 端：安裝/建庫時指定，存儲於 V$NLS_PARAMETERS.NLS_CHARACTERSET
• Client 端：由環境變量 NLS_LANG 決定（Windows 註冊表、Linux .bash_profile）
• Dump File：EXP 導出時將字符集 ID 寫入檔頭第 2~3 字節

鐵律：導入成功需 三端字符集完全一致，或 Client/Server 為 Dump File 字符集的超集。

核心參數 NLS_LANG 結構

NLS_LANG = LANGUAGE_TERRITORY.CHARACTERSET
-- 範例：AMERICAN_AMERICA.ZHS16GBK
-- 只有第三段 CHARACTERSET 影響資料存儲與轉換，前兩段僅影響提示語言、日期/數字格式

三端字符集查詢實戰

1. Server 端

SELECT USERENV('LANGUAGE') FROM DUAL;  -- 回傳如 AMERICAN_AMERICA.ZHS16GBK
-- 或
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

2. Dump File 檔頭法

-- 小檔：UltraEdit 16進制開啟，看第 2-3 字節（如 0354）
-- 大檔：Linux 命令
cat exp.dmp | od -x | head -1 | awk '{print $2 $3}' | cut -c 3-6
-- 再查對應字符集
SELECT NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX')) FROM DUAL;  -- 回傳 ZHS16GBK

3. Client 端

• Windows：註冊表 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG
• Linux：echo $NLS_LANG 或 set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK（當前 Session）

字符集修改：鐵律與禁區

⚠️ 核心原則

• **子集 → 超集** 受支援（如 US7ASCII → ZHS16GBK → UTF8）
• **超集 → 子集**、**無包含關係**（如 ZHS16GBK ↔ ZHS16CGB231280）**不支援**，強行修改會導致資料損壞、資料庫無法啟動
• 建庫後 **極不建議修改 Server 字符集**；若必須，唯一正規路徑是重建資料庫並用 Data Pump 遷移資料

Dump File 字符集「偽裝」修改（僅限救急）

-- 1. 查目標字符集 16 進制 ID
SELECT TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') FROM DUAL;  -- 0354

-- 2. 小檔：UltraEdit 直接改第 2-3 字節為 0354
-- 3. 大檔：需編寫程式（Java Stored Procedure / Python / C）修改檔頭，要求資料庫安裝 JVM

常見亂碼場景與解決對照表

SQL*Plus 亂碼快速修正

# Linux/Unix
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK      # 顯示英文介面
export NLS_LANG="SIMPLIFIED CHINESE"_CHINA.ZHS16GBK  # 顯示中文介面

# Windows CMD
SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

選型建議

中文環境首選 ZHS16GBK（涵蓋 ZHS16CGB231280，支援港台罕用字）；多語系系統建議直接採用 AL32UTF8（Oracle 推薦的 UTF-8 實作），一次到位避免後續轉換痛苦。