全面認識 Oracle 資料庫字符集：原理、查詢、修改與避坑指南

字符集核心概念

Oracle 字符集 = 位元組資料解釋的符號集合，具有大小之分與**包容關係** (子集 → 超集)。

NLS_LANG 參數結構

NLS_LANG = LANGUAGE_TERRITORY.CHARACTERSET
三個組成部分：
1. LANGUAGE  — 伺服器訊息語言 (錯誤訊息、月份名稱等)
2. TERRITORY — 日期/數字/貨幣格式
3. CHARACTERSET — **真正影響資料儲存與解釋的關鍵**

結論：兩資料庫間匯入匯出僅需 **CHARACTERSET (第三部分) 相同** 即可正確遷移資料。前兩部分僅影響顯示語系與格式。

三大字符集環境與查詢方法

1. 伺服器端字符集

-- 方法一：直觀查詢
SELECT USERENV('language') FROM dual;
-- 結果範例：AMERICAN_AMERICA.ZHS16GBK

-- 方法二：參數視圖
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
-- 結果：NLS_CHARACTERSET = ZHS16GBK

-- 方法三：Props$ 表 (內部)
SELECT * FROM SYS.PROPS$ WHERE NAME = 'NLS_CHARACTERSET';

2. 客戶端字符集

3. DMP 檔案字符集

EXP 匯出檔第 2、3 位元組記錄字符集 ID (16 進位)。

小檔案 (< 100MB) - UltraEdit 16 進位模式開啟

-- 讀取第 2、3 位元組 (如 03 54)
-- 轉換為字符集名稱
SELECT NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX')) FROM dual;
-- 結果：ZHS16GBK

大檔案 (> 2GB) - Unix 指令擷取

cat exp.dmp | od -x | head -1 | awk '{print $2 $3}' | cut -c 3-6
-- 輸出 4 位十六進位 (如 0354)
-- 再用上述 SQL 查詢名稱

字符集包容關係與修改鐵律

包容關係圖譜

US7ASCII  ⊂  WE8ISO8859P1  ⊂  ZHS16GBK / ZHS16CGB231280  ⊂  AL32UTF8 (UTF-8)
    │             │                    │                      │
  7-bit       8-bit 西歐          簡體中文 (雙位元組)          Unicode (變長 1-4 位元組)
  英文數字     西歐語系             含 GBK/GB2312              含全球所有字符

鐵律：僅支援 子集 → 超集 方向修改

警告：ZHS16GBK (GBK) 與 ZHS16CGB231280 (GB2312) **無包容關係**，官方不支援相互轉換。GBK 是 GB2312 超集，但編碼映射不同，直接修改字符集會導致中文資料亂碼。

伺服器端字符集修改 (極高風險，非必要不建議)

-- 僅在 子集→超集 且完全備份後嘗試
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; -- NCHAR/NVARCHAR2
SHUTDOWN IMMEDIATE;
STARTUP;

8i 以前 vs 9i+ 差異

• 8i 前：直接修改 `PROPS$` 表即可
• 9i+：至少 3 張系統表記錄字符集資訊，單改 `PROPS$` 會導致不一致，**必須用 ALTER DATABASE 指令**

DMP 檔案字符集修改 (風險較低，常用於匯入轉換)

原理

直接修改 DMP 第 2、3 位元組，「欺騙」 IMP 認為檔案為目標字符集。

UltraEdit 手工修改 (小檔案)

1. 用 UltraEdit 16 進位模式開啟 DMP
2. 定位第 2、3 位元組
3. 修改為目標字符集 ID (如 ZHS16GBK = 0354)
4. 儲存後 IMP 匯入

程式化修改 (大檔案)

-- Java Stored Procedure 範例 (需資料庫安裝 JVM)
-- 讀取 DMP 為 BLOB，修改位元組，寫回新檔案
-- 優點：跨平台、可處理大檔；缺點：需 JVM、程式較複雜

常見字符集 ID 對照表

實戰避坑指南

1. 建庫即決字符集：生產環境**強制統一 AL32UTF8**，避免後續轉換地獄
2. 客戶端統一 NLS_LANG：所有連線端 (應用伺服器、ETL、BI、開發工具) 必須與資料庫字符集一致
3. EXP/IMP 字符集鏈：`Server CharSet` → `Client NLS_LANG` → `DMP CharSet` **三者必須一致**才能正確匯入
4. 避免 ZHS16GBK ↔ ZHS16CGB231280 直接轉換：需透過 UTF-8 中轉或應用層重新編碼
5. NCHAR/NVARCHAR2 獨立字符集：`NLS_NCHAR_CHARACTERSET` 通常為 AL16UTF16，獨立於 `NLS_CHARACTERSET`

字符集選擇決策樹

新建資料庫
    │
    ├─ 需支援多語言/表情符/未來擴充 → **AL32UTF8** (首選)
    │
    ├─ 僅簡體中文、舊系統相容、存儲極度敏感 → ZHS16GBK
    │
    └─ 僅英文/西歐 → WE8ISO8859P1 (不推薦新系統)

字符集問題 90% 發生於「建庫時未統一規劃」與「客戶端 NLS_LANG 配置錯誤」。統一 AL32UTF8、全鏈路一致 NLS_LANG、避免事後修改，是根治之道。