字元集問題的初步探討(五)

來源:互聯網
上載者:User
問題
原文連結:


http://www.eygle.com/special/NLS_CHARACTER_SET_05.htm


原文發表於itpub技術叢書《Oracle資料庫DBA專題技術精粹》,未經許可,嚴禁轉載本文.

我們知道在匯出檔案中,記錄著匯出使用的字元集id,通過查看匯出檔案頭的第2、3個位元組,我們可以找到16進位表示的字元集ID,在Windows上,
我們可以使用UltraEdit等工具開啟dmp檔案,查看其匯出字元集::



在Unix上我們可以通過以下命令來查看:

cat expdat.dmp | od -x | head



Oracle提供標準函數,對字元集名稱及ID進行轉換:

 

 SQL> select nls_charset_id('ZHS16GBK') from dual;NLS_CHARSET_ID('ZHS16GBK')-------------------------- 8521 row selected.SQL> select nls_charset_name(852) from dual;NLS_CHAR--------ZHS16GBK1 row selected.十進位轉換十六進位:SQL> select to_char('852','xxxx') from dual;TO_CH----- 3541 row selected.

對應上面的圖中第2、3位元組,我們知道該匯出檔案字元集為ZHS16GBk.


查詢資料庫中有效字元集可以使用以下指令碼:


 

 col nls_charset_id for 9999col nls_charset_name for a30col hex_id for a20select nls_charset_id(value) nls_charset_id, value nls_charset_name,to_char(nls_charset_id(value),'xxxx') hex_idfrom v$nls_valid_valueswhere parameter = 'CHARACTERSET'order by nls_charset_id(value)/

輸出範例如下:

 

 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

..................................




在很多時候,當我們進行匯入操作的時候,已經離開了來源資料庫,這時如果目標資料庫的字元集和匯出檔案不一致,很多時候就需要進行特殊處理,
以下介紹幾種方法,主要以US7ASCII和ZHS16GBK為例

1. 來源資料庫字元集為US7ASCII,匯出檔案字元集為US7ASCII或ZHS16GBK,目標資料庫字元集為ZHS16GBK
在Oracle92中,我們發現對於這種情況,不論怎樣處理,這個匯出檔案都無法正確匯入到Oracle9i資料庫中,這可能是因為Oracle9i的編碼方案發生了較大改變。

以下是我們所做的簡單測試,其中匯出檔案命名規則為:


S-Server ,後跟Server字元集
C-client , 後跟匯出操作時用戶端字元集


匯入時用戶端字元集設定在命令列完成,限於篇幅,我們省略了部分測試過程。
對於Oracle9iR2,我們的測試結果是US7ASCII字元集,不管怎樣轉換,都無法正確匯入ZHS16GBK字元集的資料庫中。

在進行匯入操作時,如果字元不能正常轉換,Oracle資料庫會自動用一個”?”代替,也就是編碼63。


 

 E:\nls2>set NLS_LANG=AMERICAN_AMERICA.US7ASCIIE:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii.dmp fromuser=eygle touser=eygle tables=testImport: Release 9.2.0.4.0 - Production on Mon Nov 3 17:14:39 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:14:50 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select name,dump(name) from test;NAMEDUMP(NAME)-----------------------------????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,1162 rows selected.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionE:\nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii.dmp fromuser=eygle touser=eygle tables=test ignore=yImport: Release 9.2.0.4.0 - Production on Mon Nov 3 17:15:28 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character setexport client uses US7ASCII character set (possible charset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:15:34 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select name,dump(name) from test;NAMEDUMP(NAME)--------------------------------------------------------------------------------????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,116????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,1164 rows selected.SQL> drop table test;Table dropped.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionE:\nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:\nls2>imp eygle/eygle file=Sus7ascii-Czhs16gbk.dmp fromuser=eygle touser=eygle tables=test ignore=yImport: Release 9.2.0.4.0 - Production on Mon Nov 3 17:17:21 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:17:30 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select name,dump(name) from test;NAMEDUMP(NAME)----------------------------------------------????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,1162 rows selected.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionE:\nls2>set NLS_LANG=AMERICAN_AMERICA.US7ASCIIE:\nls2>imp eygle/eygle file=Sus7ascii-Czhs16gbk.dmp fromuser=eygle touser=eygle tables=test ignore=yImport: Release 9.2.0.4.0 - Production on Mon Nov 3 17:18:00 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion)export client uses ZHS16GBK character set (possible charset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:18:08 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select name,dump(name) from test;NAMEDUMP(NAME)----------------------------------------????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,116????Typ=1 Len=4: 63,63,63,63testTyp=1 Len=4: 116,101,115,1164 rows selected.SQL>

對於這種情況,我們可以通過使用Oracle8i的匯出工具,設定匯出字元集為US7ASCII,匯出後修改第二、三字元,修改 0001 為
0354,這樣就可以將US7ASCII字元集的資料正確匯入到ZHS16GBK的資料庫中。

修改匯出檔案:




匯入修改後的匯出檔案:

 

 E:\nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygle tables=testImport: Release 9.2.0.4.0 - Production on Mon Nov 3 17:37:17 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V08.01.07 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character setexport server uses UTF8 NCHAR character set (possible ncharset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:37:23 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select name,dump(name) from test;NAME DUMP(NAME)--------------------------------------------------------------------------------測試 Typ=1 Len=4: 178,226,202,212Test Typ=1 Len=4: 116,101,115,1162 rows selected.SQL>

2. 使用create database的方法
如果匯出檔案使用的字元集是US7ASCII,目標資料庫的字元集是ZHS16GBK,我們可以使用create database的方法來修改,具體如下:


 

 SQL> col parameter for a30SQL> col value for a30SQL> select * from v$nls_parameters;PARAMETER VALUE------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_CHARACTERSET ZHS16GBKNLS_SORT BINARY……………….19 rows selected.SQL> create database character set us7ascii;create database character set us7ascii*ERROR at line 1:ORA-01031: insufficient privilegesSQL> select * from v$nls_parameters;PARAMETER VALUE------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_CHARACTERSET US7ASCIINLS_SORT BINARY…………..19 rows selected.SQL> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionE:\nls2>set nls_lang=AMERICAN_AMERICA.US7ASCIIE:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii.dmp fromuser=eygle touser=eygleImport: Release 9.2.0.4.0 - Production on Sun Nov 2 14:53:26 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion). . importing table "TEST" 2 rows importedImport terminated successfully without warnings.E:\nls2>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 2 14:53:35 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select * from test;NAME----------測試test2 rows selected.

我們看到,當發出create database character set us7ascii;命令時,資料庫v$nls_parameters中的字元集設定隨之更改,該參數影響匯入進程,
更改後可以正確匯入資料,重起資料庫後,該設定恢複。

提示:v$nls_paraemters來源於x$nls_parameters,該動態效能檢視影響匯入操作;而nls_database_parameters來源於props$資料表,影響資料存放區。

3. Oracle提供的字元掃描工具csscan

我們說以上的方法只是應該在不得已的情況下使用,其本質是欺騙資料庫,強制匯入資料,可能損失中繼資料。
如果要確保資料的完整性,應該使用csscan掃描資料庫,找出所有不相容的字元,然後通過編寫相應的指令碼及代碼,在轉換之後進行更新,確保資料的正確性。
我們簡單看一下csscan的使用。

要使用csscan之前,需要以sys使用者身份建立相應資料字典對象:


 

 E:\nls2>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 2 19:42:07 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> select instance_name from v$intance;select instance_name from v$intance *ERROR at line 1:ORA-00942: table or view does not existSQL> select instance_name from v$instance;INSTANCE_NAME----------------penny1 row selected.SQL> @?/rdbms/admin/csminst.sqlUser created.Grant succeeded.………..

這個指令碼建立相應使用者(csmig)及資料字典對象,掃描資訊會記錄在相應的資料字典表裡。

我們可以在命令列調用這個工具對資料庫進行掃描:

 E:\nls2>csscan FULL=Y FROMCHAR=ZHS16GBK TOCHAR=US7ASCII LOG=US7check.log CAPTURE=Y ARRAY=1000000 PROCESS=2Character Set Scanner v1.1 : Release 9.2.0.1.0 - Production on Sun Nov 2 20:24:45 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Username: eygle/eygleConnected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionEnumerating tables to scan.... process 1 scanning SYS.SOURCE$[AAAABHAABAAAAIRAAA]. process 2 scanning SYS.ATTRIBUTE$[AAAAEoAABAAAAhZAAA]. process 2 scanning SYS.PARAMETER$[AAAAEoAABAAAAhZAAA]. process 2 scanning SYS.METHOD$[AAAAEoAABAAAAhZAAA]……... process 2 scanning SYSTEM.DEF$_AQERROR[AAAA8fAABAAACWJAAA]. process 1 scanning WMSYS.WM$ENV_VARS[AAABeWAABAAAFMZAAA]………………….. process 2 scanning SYS.UGROUP$[AAAAA5AABAAAAGpAAA]. process 2 scanning SYS.CON$[AAAAAcAABAAAACpAAA]. process 1 scanning SYS.FILE$[AAAAARAABAAAABxAAA]Creating Database Scan Summary Report...Creating Individual Exception Report...Scanner terminated successfully.

然後我們可以檢查輸出的日誌來查看資料庫掃描情況:

 Database Scan Individual Exception Report[Database Scan Parameters]Parameter Value ------------------------------ ------------------------------------------------Scan type Full database Scan CHAR data? YES Current database character set ZHS16GBK New database character set US7ASCII Scan NCHAR data? NO Array fetch buffer size 1000000 Number of processes 2 Capture convertible data? YES ------------------------------ ------------------------------------------------[Data Dictionary individual exceptions][Application data individual exceptions]User : EYGLETable : TESTColumn: NAMEType : VARCHAR2(10)Number of Exceptions : 1 Max Post Conversion Data Size: 4 ROWID Exception Type Size Cell Data(first 30 bytes) ------------------ ------------------ ----- ------------------------------AAABpIAADAAAAAMAAA lossy conversion 測試 ------------------ ------------------ ----- ------------------------------

 

不能轉換的資料將會被記錄下來,我們可以根據這些資訊在轉換之後,對資料進行相應的更新,確保轉換無誤。

 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。