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$[AAAABHAABAAAAIRAAA].process2scanningSYS.ATTRIBUTE$[AAAAEoAABAAAAhZAAA].process2scanningSYS.PARAMETER$[AAAAEoAABAAAAhZAAA].process2scanningSYS.METHOD$[AAAAEoAABAAAAhZAAA]……...process2scanningSYSTEM.DEF$_AQERROR[AAAA8fAABAAACWJAAA].process1scanningWMSYS.WM$ENV_VARS[AAABeWAABAAAFMZAAA]…………………..process2scanningSYS.UGROUP$[AAAAA5AABAAAAGpAAA].process2scanningSYS.CON$[AAAAAcAABAAAACpAAA].process1scanningSYS.FILE$[AAAAARAABAAAABxAAA]CreatingDatabaseScanSummaryReport...CreatingIndividualExceptionReport...Scannerterminatedsuccessfully.

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

DatabaseScanIndividualExceptionReport[DatabaseScanParameters]ParameterValue------------------------------------------------------------------------------ScantypeFulldatabaseScanCHARdata?YESCurrentdatabasecharactersetZHS16GBKNewdatabasecharactersetUS7ASCIIScanNCHARdata?NOArrayfetchbuffersize1000000Numberofprocesses2Captureconvertibledata?YES------------------------------------------------------------------------------[DataDictionaryindividualexceptions][Applicationdataindividualexceptions]User:EYGLETable:TESTColumn:NAMEType:VARCHAR2(10)NumberofExceptions:1MaxPostConversionDataSize:4ROWIDExceptionTypeSizeCellData(first30bytes)-----------------------------------------------------------------------AAABpIAADAAAAAMAAAlossyconversion测试-----------------------------------------------------------------------



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


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

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

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

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

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

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

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

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

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。