Oracle 中文亂碼根因分析與徹底解決方案

亂碼產生的核心原因

Oracle 中文亂碼的本質是 三端字元集不一致 導致的隱性轉換錯誤。這三端分別為：

1. 資料庫伺服器端字元集：資料實際儲存時的編碼（如 ZHS16GBK、AL32UTF8）。可用 SELECT userenv('language') FROM dual; 或查詢 props$ 確認。
2. 客戶端環境變數 NLS_LANG：告訴 Oracle Client「我用什麼編碼送資料/收資料」。Windows 存於註冊表 HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME0\NLS_LANG；Linux/Unix 需在 .profile 或 .bash_profile 設定 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK。
3. 應用程式/終端機實際編碼：如 SQL*Plus 視窗、JDBC 連線、網頁瀏覽器、批次腳本執行環境實際使用的字碼頁。

轉換機制與亂碼形成

當 資料庫字元集 ≠ NLS_LANG 字元集 時，Oracle 會依據內建對照表進行雙向轉換：

• 查詢：資料庫內部編碼 → 轉換 → NLS_LANG 編碼 → 傳給客戶端。
• 插入/更新：客戶端編碼 → 轉換 → NLS_LANG 編碼 → 轉換 → 資料庫內部編碼 → 寫入磁碟。

若對照表不正確，或兩端實際編碼與宣稱不符，轉換過程會產生錯誤碼位，造成亂碼或資料損壞。

標準解決流程

1. 確認三端一致

-- 1. 查資料庫字元集
SELECT value$ FROM props$ WHERE name = 'NLS_CHARACTERSET';

-- 2. 確認客戶端 NLS_LANG (Windows: regedit / Linux: echo $NLS_LANG)

-- 3. 確認終端機/應用程式實際編碼 (chcp / locale / 瀏覽器編碼設定)

將三者統一為同一字元集（建議新系統採用 AL32UTF8），即可避免 90% 以上亂碼問題。

2. 若必須修改資料庫字元集 (僅限舊系統遷移)

⚠ 高風險操作，務必先完整備份！

-- 僅供參考，生產環境請依官方文件執行
CONN sys/password 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 INTERNAL_USE AL32UTF8;  -- 或 ZHS16GBK
SHUTDOWN IMMEDIATE;
STARTUP;

限制條件：

• 舊字元集必須是新字元集的子集 (Subset) 才能直接 ALTER DATABASE CHARACTER SET；否則需走完整匯出/匯入或 CSALTER 腳本流程。
• 含 CLOB 欄位的資料表會阻擋修改，需先匯出該表、刪除、修改字元集後再匯入。

3. 開發環境常見陷阱：跨平台部署

案例：資料庫與應用同部署於 Solaris，建庫腳本在 Solaris 執行，Solaris 端查詢正常，但 Windows 瀏覽器顯示「？」。

原因：Solaris 終端機預設編碼 (如 ISO-8859-1) 與 Windows 瀏覽器預設 (Big5/GBK) 不同，導致 NLS_LANG 設定錯誤。

解法：在 Windows 端以正確 NLS_LANG (如 ZHS16GBK) 執行建庫腳本，或統一所有環境使用 UTF-8。

最佳實務建議

• 新專案一律採用 AL32UTF8 作為資料庫、客戶端、應用程式、網頁的統一編碼。
• 將 NLS_LANG 設定納入自動化部署腳本 (Ansible/Dockerfile/CI pipeline)，避免人為遺漏。
• 資料庫建立後立即驗證：SELECT * FROM nls_database_parameters WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
• 應用層使用 JDBC Thin Driver 時，NLS_LANG 不生效，需確保 Java file.encoding 與資料庫字元集相容，或在連線屬性指定 oracle.jdbc.defaultNChar=true。