Oracle 字元集問題總結：查詢、修改、遷移實戰指南

一、什麼是 Oracle 字元集

字元集定義位元組資料的解讀方式，具備大小與包容關係。關鍵參數 NLS_LANG 格式：

NLS_LANG = language_territory.charset
範例：AMERICAN_AMERICA.ZHS16GBK

• language：訊息語言
• territory：日期/數字格式
• charset：**真正影響資料儲存與交換的關鍵**

兩資料庫 charset 相同即可互導，前兩部分僅影響提示語言。

二、查詢三端字元集

1. Server 端

SQL> SELECT USERENV('language') FROM DUAL;
-- 結果：AMERICAN_AMERICA.ZHS16GBK

2. DMP 檔

第 2、3 位元組記錄字元集 ID (十六進位)。

• 小檔：UltraEdit 開啟十六進位模式查看位元組，如 0354
• 大檔 (＞2GB)：Unix 指令
  cat exp.dmp | od -x | head -1 | awk '{print $2 $3}' | cut -c 3-6

反查字元集名稱：

SQL> SELECT NLS_CHARSET_NAME(TO_NUMBER('0354','XXXX')) FROM DUAL;
-- ZHS16GBK

3. Client 端

• Windows：登錄表 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME[ID]\NLS_LANG
• Unix/Linux：環境變數 $NLS_LANG
• 臨時變更 (當前 Session)：
  SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK (Windows)
  export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK (Unix)

**三端必須一致** 才能正確匯入匯出。

三、修改字元集

原則

• 字元集具**子集→超集**包容關係 (如 US7ASCII ⊂ ZHS16GBK ⊂ UTF8)
• **僅支援子集轉超集**，反向或無包容關係 (如 ZHS16GBK ↔ ZHS16CGB231280) **官方不支援**
• 建庫後字元集理論上不可變，**極謹慎操作**

1. Server 端修改 (不建議，風險極高)

SQLPLUS /NOLOG
CONN / AS SYSDBA
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$，8 以後至少三張系統表記錄，單改 PROPS$ 會導致嚴重不一致。

2. DMP 檔字元集修改 (常用技巧，僅騙過檢查)

• 小檔：UltraEdit 直接修改第 2、3 位元組為目標字元集 ID (如 0354)
• 大檔：需撰寫程式 (Java Stored Procedure 等) 修改位元組
• **理論僅限子集→超集**，但實測 US7ASCII、WE8ISO8859P1、ZHS16CGB231280、ZHS16GBK 互改多數成功，風險自負

查詢目標字元集 ID：

SQL> SELECT TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') FROM DUAL;
-- 0354

遷移建議

1. 新建資料庫時**直接選正確字元集** (建議 UTF8/AL32UTF8)
2. 跨字元集遷移：建立目標字元集新庫，用 EXP/IMP 或 Data Pump 遷移資料
3. 應用層統一設定 NLS_LANG 與資料庫一致