变相怪杰 发表于 2015-1-16 22:34:09

MSSQL网站制作之字符集成绩的开端切磋(五)

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。索引不是万能的,索引可以加快数据检索操作,但会使数据修改操作变慢。每修改数据记录,索引就必须刷新一次。成绩
原文链接:


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


原文宣布于itpub手艺丛书《Oracle数据库DBA专题手艺精炼》,未经允许,严禁转载本文.

我们晓得在导出文件中,纪录着导出利用的字符集id,经由过程检察导出文件头的第2、3个字节,我们能够找到16进制暗示的字符集ID,在Windows上,
我们可使用UltraEdit等工具翻开dmp文件,检察其导出字符集::



在Unix上我们能够经由过程以下命令来检察:

catexpdat.dmp|od-x|head



Oracle供应尺度函数,对字符集称号及ID举行转换:



SQL>selectnls_charset_id(ZHS16GBK)fromdual;NLS_CHARSET_ID(ZHS16GBK)--------------------------8521rowselected.SQL>selectnls_charset_name(852)fromdual;NLS_CHAR--------ZHS16GBK1rowselected.十进制转换十六进制:SQL>selectto_char(852,xxxx)fromdual;TO_CH-----3541rowselected.

对应下面的图中第2、3字节,我们晓得该导出文件字符集为ZHS16GBk.


查询数据库中无效的字符集可使用以下剧本:




colnls_charset_idfor9999colnls_charset_namefora30colhex_idfora20selectnls_charset_id(value)nls_charset_id,valuenls_charset_name,to_char(nls_charset_id(value),xxxx)hex_idfromv$nls_valid_valueswhereparameter=CHARACTERSETorderbynls_charset_id(value)/

输入样比方下:



NLS_CHARSET_IDNLS_CHARSET_NAMEHEX_ID
---------------------------------------------------------
1US7ASCII1
2WE8DEC2
3WE8HP3
4US8PC4374
5WE8EBCDIC375
6WE8EBCDIC5006
7WE8EBCDIC11407
8WE8EBCDIC2858
...................
850ZHS16CGB231280352
851ZHS16MACCGB231280353
852ZHS16GBK354
853ZHS16DBCS355
860ZHT32EUC35c
861ZHT32SOPS35d
862ZHT16DBT35e
863ZHT32TRIS35f
864ZHT16DBCS360
865ZHT16BIG5361
866ZHT16CCDC362
867ZHT16MSWIN950363
868ZHT16HKSCS364
870AL24UTFFSS366
871UTF8367
872UTFE368

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




在良多时分,当我们举行导进操纵的时分,已分开了源数据库,这时候假如方针数据库的字符集和导出文件纷歧致,良多时分就必要举行特别处置,
以下先容几种办法,次要以US7ASCII和ZHS16GBK为例

1.源数据库字符集为US7ASCII,导出文件字符集为US7ASCII或ZHS16GBK,方针数据库字符集为ZHS16GBK
在Oracle92中,我们发明关于这类情形,不管如何处置,这个导出文件都没法准确导进到Oracle9i数据库中,这多是由于Oracle9i的编码计划产生了较年夜改动。

以下是我们所做的复杂测试,个中导出文件定名划定规矩为:


S-Server,后跟Server字符集
C-client,后跟导出操纵时客户端字符集


导进时客户端字符集设置在命令行完成,限于篇幅,我们省略了部分测试历程。
关于Oracle9iR2,我们的测试了局是US7ASCII字符集,不论如何转换,都没法准确导进ZHS16GBK字符集的数据库中。

在举行导进操纵时,假如字符不克不及一般转换,Oracle数据库会主动用一个”?”取代,也就是编码63。




E:
ls2>setNLS_LANG=AMERICAN_AMERICA.US7ASCIIE:
ls2>impeygle/eyglefile=Sus7ascii-Cus7ascii.dmpfromuser=eygletouser=eygletables=testImport:Release9.2.0.4.0-ProductiononMonNov317:14:392003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V09.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(possiblecharsetconversion)..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononMonNov317:14:502003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectname,dump(name)fromtest;NAMEDUMP(NAME)-----------------------------????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,1162rowsselected.SQL>exitDisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionE:
ls2>setNLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:
ls2>impeygle/eyglefile=Sus7ascii-Cus7ascii.dmpfromuser=eygletouser=eygletables=testignore=yImport:Release9.2.0.4.0-ProductiononMonNov317:15:282003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V09.02.00viaconventionalpathimportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharactersetexportclientusesUS7ASCIIcharacterset(possiblecharsetconversion)..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononMonNov317:15:342003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectname,dump(name)fromtest;NAMEDUMP(NAME)--------------------------------------------------------------------------------????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,116????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,1164rowsselected.SQL>droptabletest;Tabledropped.SQL>exitDisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionE:
ls2>setNLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:
ls2>impeygle/eyglefile=Sus7ascii-Czhs16gbk.dmpfromuser=eygletouser=eygletables=testignore=yImport:Release9.2.0.4.0-ProductiononMonNov317:17:212003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V09.02.00viaconventionalpathimportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharacterset..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononMonNov317:17:302003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectname,dump(name)fromtest;NAMEDUMP(NAME)----------------------------------------------????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,1162rowsselected.SQL>exitDisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionE:
ls2>setNLS_LANG=AMERICAN_AMERICA.US7ASCIIE:
ls2>impeygle/eyglefile=Sus7ascii-Czhs16gbk.dmpfromuser=eygletouser=eygletables=testignore=yImport:Release9.2.0.4.0-ProductiononMonNov317:18:002003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V09.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(possiblecharsetconversion)exportclientusesZHS16GBKcharacterset(possiblecharsetconversion)..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononMonNov317:18:082003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectname,dump(name)fromtest;NAMEDUMP(NAME)----------------------------------------????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,116????Typ=1Len=4:63,63,63,63testTyp=1Len=4:116,101,115,1164rowsselected.SQL>

关于这类情形,我们能够经由过程利用Oracle8i的导收工具,设置导出字符集为US7ASCII,导出后修正第2、三字符,修正0001为
0354,如许就能够将US7ASCII字符集的数据准确导进到ZHS16GBK的数据库中。

修正导出文件:




导进修正后的导出文件:



E:
ls2>setNLS_LANG=AMERICAN_AMERICA.ZHS16GBKE:
ls2>impeygle/eyglefile=Sus7ascii-Cus7ascii-exp817.dmpfromuser=eygletouser=eygletables=testImport:Release9.2.0.4.0-ProductiononMonNov317:37:172003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V08.01.07viaconventionalpathimportdoneinZHS16GBKcharactersetandAL16UTF16NCHARcharactersetexportserverusesUTF8NCHARcharacterset(possiblencharsetconversion)..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononMonNov317:37:232003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectname,dump(name)fromtest;NAMEDUMP(NAME)--------------------------------------------------------------------------------测试Typ=1Len=4:178,226,202,212TestTyp=1Len=4:116,101,115,1162rowsselected.SQL>

2.利用createdatabase的办法
假如导出文件利用的字符集是US7ASCII,方针数据库的字符集是ZHS16GBK,我们可使用createdatabase的办法来修正,详细以下:




SQL>colparameterfora30SQL>colvaluefora30SQL>select*fromv$nls_parameters;PARAMETERVALUE------------------------------------------------------------NLS_LANGUAGEAMERICANNLS_TERRITORYAMERICANLS_CURRENCY$NLS_ISO_CURRENCYAMERICANLS_NUMERIC_CHARACTERS.,NLS_CALENDARGREGORIANNLS_DATE_FORMATDD-MON-RRNLS_DATE_LANGUAGEAMERICANNLS_CHARACTERSETZHS16GBKNLS_SORTBINARY……………….19rowsselected.SQL>createdatabasecharactersetus7ascii;createdatabasecharactersetus7ascii*ERRORatline1:ORA-01031:insufficientprivilegesSQL>select*fromv$nls_parameters;PARAMETERVALUE------------------------------------------------------------NLS_LANGUAGEAMERICANNLS_TERRITORYAMERICANLS_CURRENCY$NLS_ISO_CURRENCYAMERICANLS_NUMERIC_CHARACTERS.,NLS_CALENDARGREGORIANNLS_DATE_FORMATDD-MON-RRNLS_DATE_LANGUAGEAMERICANNLS_CHARACTERSETUS7ASCIINLS_SORTBINARY…………..19rowsselected.SQL>exitDisconnectedfromOracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionE:
ls2>setnls_lang=AMERICAN_AMERICA.US7ASCIIE:
ls2>impeygle/eyglefile=Sus7ascii-Cus7ascii.dmpfromuser=eygletouser=eygleImport:Release9.2.0.4.0-ProductiononSunNov214:53:262003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionExportfilecreatedbyEXPORT:V09.02.00viaconventionalpathimportdoneinUS7ASCIIcharactersetandAL16UTF16NCHARcharactersetimportserverusesZHS16GBKcharacterset(possiblecharsetconversion)..importingtable"TEST"2rowsimportedImportterminatedsuccessfullywithoutwarnings.E:
ls2>sqlpluseygle/eygleSQL*Plus:Release9.2.0.4.0-ProductiononSunNov214:53:352003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>select*fromtest;NAME----------测试test2rowsselected.

我们看到,当收回createdatabasecharactersetus7ascii;命令时,数据库v$nls_parameters中的字符集设置随之变动,该参数影响导进历程,
变动后能够准确导进数据,重起数据库后,该设置恢复。

提醒:v$nls_paraemters来历于x$nls_parameters,该静态功能视图影响导进操纵;而nls_database_parameters来历于props$数据表,影响数据存储。

3.Oracle供应的字符扫描工具csscan

我们说以上的办法只是应当在不得已的情形下利用,其实质是棍骗数据库,强迫导进数据,大概丧失元数据。
假如要确保数据的完全性,应当利用csscan扫描数据库,找出一切不兼容的字符,然后经由过程编写响应的剧本及代码,在转换以后举行更新,确保数据的准确性。
我们复杂看一下csscan的利用。

要利用csscan之前,必要以sys用户身份创立响应数据字典工具:




E:
ls2>sqlplus"/assysdba"SQL*Plus:Release9.2.0.4.0-ProductiononSunNov219:42:072003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionSQL>selectinstance_namefromv$intance;selectinstance_namefromv$intance*ERRORatline1:ORA-00942:tableorviewdoesnotexistSQL>selectinstance_namefromv$instance;INSTANCE_NAME----------------penny1rowselected.SQL>@?/rdbms/admin/csminst.sqlUsercreated.Grantsucceeded.………..

这个剧本创立响应用户(csmig)及数据字典工具,扫描信息会纪录在响应的数据字典内外。

我们能够在命令行挪用这个工具对数据库举行扫描:

E:
ls2>csscanFULL=YFROMCHAR=ZHS16GBKTOCHAR=US7ASCIILOG=US7check.logCAPTURE=YARRAY=1000000PROCESS=2CharacterSetScannerv1.1:Release9.2.0.1.0-ProductiononSunNov220:24:452003Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.Username:eygle/eygleConnectedto:Oracle9iEnterpriseEditionRelease9.2.0.4.0-ProductionWiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.4.0-ProductionEnumeratingtablestoscan....process1scanningSYS.SOURCE$.process2scanningSYS.ATTRIBUTE$.process2scanningSYS.PARAMETER$.process2scanningSYS.METHOD$……...process2scanningSYSTEM.DEF$_AQERROR.process1scanningWMSYS.WM$ENV_VARS…………………..process2scanningSYS.UGROUP$.process2scanningSYS.CON$.process1scanningSYS.FILE$CreatingDatabaseScanSummaryReport...CreatingIndividualExceptionReport...Scannerterminatedsuccessfully.

然后我们能够反省输入的日记来检察数据库扫描情形:

DatabaseScanIndividualExceptionReportParameterValue------------------------------------------------------------------------------ScantypeFulldatabaseScanCHARdata?YESCurrentdatabasecharactersetZHS16GBKNewdatabasecharactersetUS7ASCIIScanNCHARdata?NOArrayfetchbuffersize1000000Numberofprocesses2Captureconvertibledata?YES------------------------------------------------------------------------------User:EYGLETable:TESTColumn:NAMEType:VARCHAR2(10)NumberofExceptions:1MaxPostConversionDataSize:4ROWIDExceptionTypeSizeCellData(first30bytes)-----------------------------------------------------------------------AAABpIAADAAAAAMAAAlossyconversion测试-----------------------------------------------------------------------



不克不及转换的数据将会被纪录上去,我们能够依据这些信息在转换以后,对数据举行响应的更新,确保转换无误。


修复过程包含最多4个阶段,在下面描述。在你开始前,你应该cd到数据库目录和检查表文件的权限,确保他们可被运行mysqld的Unix用户读取(和你,因为你需要存取你正在检查的文件)。如果它拒绝你修改文件,他们也必须是可被你写入的。

简单生活 发表于 2015-1-19 16:34:13

而SQLServer如果能像Oracle一样可以为登陆分配如:5%的cpu,10%的内存。就可以解决这个漏洞。

灵魂腐蚀 发表于 2015-1-27 17:20:20

比如日志传送、比如集群。。。

透明 发表于 2015-2-5 10:31:17

从项目平台的选择上讲,我们关心的,应该是一款产品能不能满足任务需求,而不是网上怎么说。

兰色精灵 发表于 2015-2-11 09:32:02

我们学到了什么?思考问题的时候从表的角度来思考问

金色的骷髅 发表于 2015-3-2 09:59:59

我是新手,正在学习数据库和操作系统,深感理论的泛广,唯有一步一步来,但是又感觉时间不够,收集了很多资料却总是没能认真的看完,希望有一个讨论板块,大家共同解决,共同分享,共同努力

海妖 发表于 2015-3-11 03:11:35

作了些试验,发现使用CLR的存储过程或函数在达到一定的阀值的时候,系统性能会呈指数级下滑!这是非常危险的!只使用几个可能没有问题,当一旦大规模使用会造成严重的系统性能问题!

飘飘悠悠 发表于 2015-3-17 19:44:33

XML字段类型更好的解决了XML数据的操作。XQuery确实不错,但是个人对其没好感。(CSDN的开发者应该是相当的熟了!)

再现理想 发表于 2015-3-24 23:01:16

分区表效率问题肯定是大家关心的问题。在我的试验中,如果按照分区字段进行的查询(过滤)效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询,效率会低于未分区表的相同语句。
页: [1]
查看完整版本: MSSQL网站制作之字符集成绩的开端切磋(五)