Oracle 字元集完整指南：查詢、相容性、修改與避坑實戰

一、 核心參數：NLS_LANG 三要素

NLS_LANG = language_territory.charset

• Language：訊息語言（如 AMERICAN）
• Territory：日期/數字格式（如 AMERICA）
• Charset：唯一影響資料儲存與交換的關鍵（如 ZHS16GBK、AL32UTF8）

兩資料庫間導入導出，僅需 charset 相同；前兩項僅影響提示訊息語系。

二、 三端字元集一致性原則

資料遷移（exp/imp、Data Pump）需三端字元集一致：

1. Server 端：資料庫實際儲存編碼
2. Client 端：應用/工具連線環境的 NLS_LANG
3. Dump 檔：匯出檔內嵌的字元集標記

不一致會導致亂碼或 IMP-00031 錯誤。

三、 實戰查詢技巧

1. Server 端

SELECT userenv('language') FROM dual;
-- 回傳：AMERICAN_AMERICA.ZHS16GBK

2. Dump 檔（關鍵：第 2、3 位元組）

小檔案：UltraEdit 開啟十六進制，讀取位元組（如 0354）→ 轉換：

SELECT nls_charset_name(TO_NUMBER('0354','XXXX')) FROM dual;
-- 回傳：ZHS16GBK

大檔案（>2GB）：Linux 指令擷取：

cat exp.dmp | od -x | head -1 | awk '{print $2 $3}' | cut -c 3-6

3. Client 端

• Windows：註冊表 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_\NLS_LANG
• 臨時變更：SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK（僅影響當前 CMD 視窗）
• Linux/Unix：echo $NLS_LANG 或 export NLS_LANG=...

四、 字元集相容與轉換規則

• 子集 → 超集：支援，無資料損失（如 US7ASCII → ZHS16GBK → AL32UTF8）。
• 超集 → 子集：不支援，資料截斷/遺失。
• 無包含關係：官方不支援（如 ZHS16GBK ↔ ZHS16CGB231280）。

鐵律：資料庫建立後理論上不可變更字元集。設計階段務必選擇通用字元集（強烈建議 AL32UTF8）。

五、 修改字元集（高風險，僅限測試/特殊救援）

Server 端（不建議）

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET ZHS16GBK;  -- 僅限子集→超集
ALTER DATABASE NATIONAL CHARACTER SET ZHS16GBK;
SHUTDOWN IMMEDIATE;
STARTUP;

Oracle 8+ 至少三張系統表記錄字元集，直接改 props$ 會導致資料庫損壞。

Dump 檔修改（相對安全，常用於匯入救援）

1. 查目標字元集十六進制：SELECT TO_CHAR(nls_charset_id('ZHS16GBK'),'XXXX') FROM dual; → 0354
2. 小檔：UltraEdit 修改第 2、3 位元組為 0354。
3. 大檔：編寫程式（Java Stored Procedure / C / Python）直接修改二進位位元組。

實測：US7ASCII、WE8ISO8859P1、ZHS16CGB231280、ZHS16GBK 互改多半成功，但僅欺騙匯入檢查，資料實際編碼未變，若實際編碼不符仍會產生亂碼。

六、 最佳實踐建議

1. 新建資料庫統一採用 AL32UTF8，支援全語系、避免轉換痛苦。
2. Client 端 NLS_LANG 必須與 Server charset 一致。
3. 遷移前務必三端核對，必要時用 Character Set Scanner (csscan) 掃描風險資料。
4. 不可輕易修改 Server 字元集，若必須轉換，採用「匯出→建新庫→匯入」正規流程。