查看Oracle字元集及怎樣修改字元集（zt）

一、什麼是oracle字元集 　　

Oracle字元集是一個位元組資料的解釋的符號集合,有大小之分,有相互的包容關係。ORACLE
支援國家語言的體繫結構允許你使用本地化語言來儲存，處理，檢索資料。它使資料庫工具，錯誤訊息，排序次序，日期，時間，貨幣，數字，和日曆自動適應本地
化語言和平台。 　　

影響oracle資料庫字元集最重要的參數是NLS_LANG參數。

它的格式如下: 　　NLS_LANG = language_territory.charset 　　

它有三個組成部分(語言、地區和字元集)，每個成分控制了NLS子集的特性。

其中: 　　

Language 指定伺服器訊息的語言，territory 指定伺服器的日期和數字格式，charset 指定字元集。如:AMERICAN _ AMERICA. ZHS16GBK 　　

從NLS_LANG的組成我們可以看出，真正影響資料庫字元集的其實是第三部分。

所以兩個資料庫之間的字元集只要第三部分一樣就可以相互匯入匯出資料，前面影響的只是提示資訊是中文還是英文。

 

二.查看資料庫字元集

這涉及三方面的字元集，

一是oracel server端的字元集;

二是oracle client端的字元集;

三是dmp檔案的字元集。

 

在做資料匯入的時候，需要這三個字元集都一致才能正確匯入。 　

　1、查詢oracle server端的字元集 　　

       有很多種方法可以查出oracle server端的字元集，比較直觀的查詢方法是以下這種:

      SQL>select userenv(‘language’) from dual; 　　

      結果類似如下:AMERICAN _ AMERICA. ZHS16GBK 　　

   2、如何查詢dmp檔案的字元集 　　

     
用oracle的exp工具匯出的dmp檔案也包含了字元集資訊，dmp檔案的第2和第3個位元組記錄了dmp檔案的字元集。如果dmp檔案不大，比如只有
幾M或幾十M，可以用UltraEdit開啟(16進位方式)，看第2第3個位元組的內容，如0354，然後用以下SQL查出它對應的字元集: 　　

      SQL> select nls_charset_name(to_number('0354','xxxx')) from dual; 　　

        ZHS16GBK 　　

     如果dmp檔案很大，比如有2G以上(這也是最常見的情況)，用文字編輯器開啟很慢或者完全打不開，可以用以下命令(在unix主機上): 　　

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

     然後用上述SQL也可以得到它對應的字元集。

3、查詢oracle client端的字元集 　　

這個比較簡單。

在windows平台下，就是註冊表裡面相應OracleHome的NLS_LANG。還可以在dos視窗裡面自己設定，比如: 　　set nls_lang=AMERICAN_AMERICA.ZHS16GBK 　　

這樣就隻影響這個視窗裡面的環境變數。 　　

在unix平台下，就是環境變數NLS_LANG。 　　

$echo $NLS_LANG 　　

AMERICAN_AMERICA.ZHS16GBK 　　

如果檢查的結果發現server端與client端字元集不一致，請統一修改為同server端相同的字元集。

 

補充：

(1).資料庫伺服器字元集

select * from nls_database_parameters

來源於props$，是表示資料庫的字元集。

(2).用戶端字元集環境

select * from nls_instance_parameters

其來源於v$parameter，表示用戶端的字元集的設定，可能是參數檔案，環境變數或者是註冊表

(3).會話字元集環境

select * from nls_session_parameters

來源於v$nls_parameters，表示會話自己的設定，可能是會話的環境變數或者是alter session完成，如果會話沒有特殊的設定，將與nls_instance_parameters一致。

(4).用戶端的字元集要求與伺服器一致，才能正確顯示資料庫的非Ascii字元。如果多個設定存在的時候，alter session>環境變數>註冊表>參數檔案

字元集要求一致，但是語言設定卻可以不同，語言設定建議用英文。如字元集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。

 

三、修改oracle的字元集 　　

上文說過，oracle的字元集有互相的包容關係。如us7ascii就是zhs16gbk的子集,從us7ascii到zhs16gbk不會有數
據解釋上的問題,不會有資料丟失。在所有的字元集中utf8應該是最大,因為它基於unicode,雙位元組儲存字元(也因此在儲存空間上佔用更多)。
　　

一旦資料庫建立後，資料庫的字元集理論上講是不能改變的。因此，在設計和安裝之初考慮使用哪一種字元集十分重要。根據Oracle的官方說明，字元
集的轉換是從子集到超集受支援,反之不行。如果兩種字元集之間根本沒有子集和超集的關係，那麼字元集的轉換是不受oracle支援的。對資料庫
server而言，錯誤的修改字元集將會導致很多不可測的後果，可能會嚴重影響資料庫的正常運行，所以在修改之前一定要確認兩種字元集是否存在子集和超集
的關係。一般來說，除非萬不得已，我們不建議修改oracle資料庫server端的字元集。特別說明，我們最常用的兩種字元集ZHS16GBK和
ZHS16CGB231280之間不存在子集和超集關係，因此理論上講這兩種字元集之間的相互轉換不受支援。 　　

1、修改server端字元集(不建議使用) 　

　在oracle 8之前，可以用直接修改資料字典表props$來改變資料庫的字元集。但oracle8之後，至少有三張系統資料表記錄了資料庫字元集的資訊，只改props$表並不完全，可能引起嚴重的後果。正確的修改方法如下:

$sqlplus /nolog 　　

SQL>conn / as sysdba; 　　若此時資料庫伺服器已啟動，則先執行SHUTDOWN IMMEDIATE命令關閉資料庫伺服器，然後執行以下命令: 　

SQL>STARTUP MOUNT; 　　

SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION; 　　

SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 　　

SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0; 　　

SQL>ALTER DATABASE OPEN; 　　

SQL>ALTER DATABASE CHARACTER SET ZHS16GBK; 　　

SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK; 　　

SQL>SHUTDOWN IMMEDIATE; 　　

SQL>STARTUP

 

注意：如果沒有大對象，在使用過程中進行語言轉換沒有什麼影響，（切記設定的字元集必須是ORACLE支援，不然不能start）
按上面的做法就可以,但是可能會出現‘ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER
SET when NCLOB data exists’ 這樣的提示資訊

 

要解決這個問題有兩種方法

一個是,利用INTERNAL_USE 關鍵字修改地區設定,

還有一個是利用re-create,但是re-create有點複雜,所以請用internal_use,

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT EXCLUSIVE;

SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL>ALTER DATABASE OPEN;

SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE

UTF8;

SQL>SHUTDOWN immediate;

SQL>startup;

如果按上面的做法做,National charset的地區設定就沒有問題

2、修改dmp檔案字元集 　　

上文說過，dmp檔案的第2第3位元組記錄了字元集資訊，因此直接修改dmp檔案的第2第3位元組的內容就可以‘騙’過oracle的檢查。這樣做理論
上也僅是從子集到超集可以修改，但很多情況下在沒有子集和超集關係的情況下也可以修改，我們常用的一些字元集，如
US7ASCII，WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因為改的只是dmp檔案，所以影響不大。
　　

具體的修改方法比較多，最簡單的就是直接用UltraEdit修改dmp檔案的第2和第3個位元組。

比如想將dmp檔案的字元集改為ZHS16GBK，可以用以下SQL查出該種字元集對應的16進位代碼: 　　SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual; 　　

0354 　　

然後將dmp檔案的2、3位元組修改為0354即可。 　　

如果dmp檔案很大，用ue無法開啟，就需要用程式的方法了。

-----------------------------------------------------------------------------------------------------------------------------------

資料庫伺服器字元集更改步驟

　　問題描述：

　　在用戶端插入字元“咪咪”，從資料庫中查詢顯示時出現亂碼

　　處理步驟：

　　10.1 對資料庫做全庫匯出，備份全庫資料，以防故障發生

　　首先設定用戶端的字元集，必須以ZHS16GBK的字元集匯出，然後才能在更改失敗後順利倒入建立的庫。

　　#setenv NLS_LANG "SIMPLIFIED CHINESE_CHINA.ZHS16GBK";

　　#stty -istrip -parity cs8;

　　#setenv LANG zh

　　擬在/sybdata(磁碟陣列)下建立一個目錄orabak,用於存放dmp檔案。

　　#mkdir /sybdata/orabak

　　#chown oracle:oinstall /sybdata/orabak

　　#su – oracle

　　#cd /sybdata/orabak

　　%exp system/manager@hnsdh
file=hnsdh_2005-8-17 log=hnsdh_exp_2005-8-17 full=y

　　(此處命名為樣本，以實施當日日期為準)

　　察看日誌結尾，以判定匯出是否成功。

　　#cat hnsdh_2005-8-17.dmp | od -x | head

　　看第二和第三個位元組組成的十六進位數是多少可判斷匯出檔案的字元集。

　　樣本如下

　　#cat example.dmp | od -x | head
　　0000000 0303 5445 5850 4f52 543a 5630 392e 3032
　　。。。
　　0000220 646d 7000 0000 0000 0000 0000 0000 0000
　　十六進位的0354化為十進位為852，參造下表
　　NLS_CHARSET_ID NLS_CHARSET_NAME HEX_ID
　　-------------- ------------------------------ -------------
　　1 US7ASCII 1
　　2 WE8DEC 2
　　3 WE8HP 3
　　4 US8PC437 4
　　5 WE8EBCDIC37 5
　　6 WE8EBCDIC500 6
　　7 WE8EBCDIC1140 7
　　8 WE8EBCDIC285 8
　　...................
　　850 ZHS16CGB231280 352
　　851 ZHS16MACCGB231280 353
　　852 ZHS16GBK 354
　　853 ZHS16DBCS 355
　　860 ZHT32EUC 35c
　　861 ZHT32SOPS 35d
　　862 ZHT16DBT 35e
　　863 ZHT32TRIS 35f
　　864 ZHT16DBCS 360
　　865 ZHT16BIG5 361
　　866 ZHT16CCDC 362
　　867 ZHT16MSWIN950 363
　　868 ZHT16HKSCS 364
　　870 AL24UTFFSS 366
　　871 UTF8 367
　　872 UTFE 368

　　即可得出這個dmp檔案的字元集為ZHS16GBK。

　　10.2 在資料庫中直接更改字元集參數

　　操作步驟如下：

　　SQL> shutdown immediate
　　SQL> startup mount
　　SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
　　SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
　　SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
　　SQL> ALTER DATABASE OPEN;
　　SQL> alter session set events '10046 trace name context forever,level 12';
　　SQL> alter database character set INTERNAL_USE ZHS16GBK;
　　SQL> shutdown immediate
　　SQL> startup

　　察看系統字元集

　　SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

　　看NLS_CHARACTERSET的值為多少，如果為ZHS16GBK則說明改動成功。

　　如果執行正常，則按照下一節進行測試操作。

　　10.3 更改成功後的測試

　　測試1，在資料庫伺服器端下測試

　　%setenv NLS_LANG "SIMPLIFIED CHINESE_CHINA.ZHS16GBK";
　　%stty -istrip -parity cs8;
　　%setenv LANG zh
　　%sqlplus /nolog
　　SQL〉conn / as sysdba
　　SQL〉create table test_tq (a char(20));
　　SQL〉insert into test_tq
　　1>(a)
　　2>values ('洣洣');
　　SQL〉select * from test_tq;

　　如顯示為

　　A

　　--------------------

　　洣洣

　　則成功。

　　測試2，Windows用戶端環境下測試
　　運 行REGEDIT,第一步選HKEY_LOCAL_MACHINE,第二步選擇SOFTWARE， 第三步選擇 ORACLE， 第四步選擇
NLS_LANG， 鍵 入 與服 務 器 端 相 同 的 字 符 集(本例為：AMERICAN_AMERICAN.US7ASCII)。

　　右擊我的電腦，然後點擊屬性，“進階”頁面下，點擊“環境變數”，在系統變數中添加：

　　變數名：NLS_LANG

　　變數值:SIMPLIFIED CHINESE_CHINA.ZHS16GBK

　　運行cmd，輸入echo %NLS_LANG%，查看系統變數設定時否成功

　　然後運行：

　　$sqlplus system/manager@hnsdh

　　SQL〉conn / as sysdba
　　SQL〉create table test_tq (a char(20));
　　SQL〉insert into test_tq
　　1>(a)
　　2>values ('洣洣');
　　SQL〉select * from test_tq;

　　如顯示為

　　A

　　--------------------

　　洣洣

　　則成功。

　　10.4 更改不成功時的措施

　　建立資料庫，設定字元集為ZHS16GBK,其他參數先照搬原來的，並倒入資料。建庫時所需的具體參數在重建之前要搜集。注意在配置控制檔案時設定最大資料檔案數。

　　建好資料庫以後，執行以下命令即可恢複資料庫

　　%cd /sybdata/orabak

　　%imp system/manager@hnsdh
full=y ignore=y file=hnsdh_2005-8-17 log=hnsdh_imp_2005 -8-17