Oracle字符集问题的初步探讨.docx
《Oracle字符集问题的初步探讨.docx》由会员分享，可在线阅读，更多相关《Oracle字符集问题的初步探讨.docx（40页珍藏版）》请在冰豆网上搜索。
Oracle字符集问题的初步探讨
字符集问题的初步探讨
Oracle全球支持（即GlobalizationSupport）允许我们使用本地语言和格式来存储和检索数据。
通过全球支持，Oracle可以支持多种语言及字符集，得以展示数据库的强大魅力。
由于不同语言及字符集的共同存储存在设置上具有一定的复杂性，字符集一度成为普遍困扰大家的一个主要问题。
本文试图就一些常见问题进行探讨，希望可以把一些实际经验共享给大家！
字符集的基本知识
如果从头说起，字符集最早的编码方案来自于与ASCII.
这也是我们最常见的编码方式。
该方案起源于1960年代初期，最初是美国国会图书馆制定用来作为美国图书馆界书目交换的共同标准，最后完善成为美国的国家标准ASCII（AmericanStandardCodeforInformationInterchange），之后进一步演变成世界性的计算机字符编码标准ISO646（其全名为7-bitcodedcharactersetforinformationinterchange）。
成为计算机编码方案的基础。
Oracle数据库最早支持的编码方案也就是US7ASCII.
但是我们知道，英文字符一般是以一个字节来存储的，7位的编码方案最多只能代表128个字符；经过扩展的8位的编码方案也只能代表256个字符，这远远不能满足计算机发展的需要，对于亚洲国家复杂的字符存储需要更多的码位，于是各种编码方案随之而生。
为了容纳全世界各种语言的所有字符和符号，解决不同编码之间的兼容和转换问题，1991年元月，10多家公司共同出资，组建Unicode协会,随后Unicode编码产生了。
Unicode协会的口号是：
给每个字符提供了一个唯一的数字，不论是什么平台，不论是什么程序，不论什么语言。
最初Unicode编码使用2-Byte（16bit）来进行编码，但是最多只能容纳65536个字符，仍然不够使用，后来进行了扩充，也就是Unicode3.1标准，增加了额外的补充字符定义，现在Unicode4.0标准已经发布，具体可以参考Unicode官方站点:
www.unicode.org
Unicode编码方案主要有三个实施标准:
UTF-8
USC-2
UTF-16
Oracle从7.2开始支持UTF-8编码，提供Unicode编码支持。
按照各种标准的含义，Oracle推荐，如果你的数据库需要存放不同语言的不同符号和字符，建议使用Unicode编码方案。
诚然，Unicode方案可以表示更多的字符，但是由于多位的存储，需要额外的存储空间和网络传输，所以选择最适合的数据库字符集仍然需要慎重考虑。
数据库的字符集
字符集在创建数据库时指定，在创建后通常不能更改，所以在创建数据库时能否选择一个正确的字符集就显得尤为重要。
在创建数据库时，我们可以指定字符集（CHARACTERSET）和国家字符集（NATIONALCHARACTERSET）。
字符集用来存储:
CHAR,VARCHAR2,CLOB,LONG等类型数据
用来标示诸如表名、列名以及PL/SQL变量等
SQL和PL/SQL程序单元等
国家字符集用以存储:
NCHAR,NVARCHAR2,NCLOB等类型数据
这些设置在数据库创建时指定，我们可以看一下数据库的创建脚本:
connectSYS/change_on_installasSYSDBA
setechoon
spoolE:
\oracle\ora92\assistants\dbca\logs\CreateDB.log
startupnomountpfile="E:
\oracle\admin\eygle\scripts\init.ora";
CREATEDATABASEeygle
MAXINSTANCES1
MAXLOGHISTORY1
MAXLOGFILES5
MAXLOGMEMBERS3
MAXDATAFILES100
DATAFILE'E:
\oracle\oradata\eygle\system01.dbf'SIZE250MREUSEAUTOEXTENDONNEXT10240KMAXSIZEUNLIMITED
EXTENTMANAGEMENTLOCAL
DEFAULTTEMPORARYTABLESPACETEMPTEMPFILE'E:
\oracle\oradata\eygle\temp01.dbf'SIZE40MREUSEAUTOEXTENDONNEXT640KMAXSIZEUNLIMITED
UNDOTABLESPACE"UNDOTBS1"DATAFILE'E:
\oracle\oradata\eygle\undotbs01.dbf'SIZE50MREUSEAUTOEXTENDONNEXT5120KMAXSIZEUNLIMITED
CHARACTERSETZHS16GBK
NATIONALCHARACTERSETAL16UTF16
LOGFILEGROUP1（'E:
\oracle\oradata\eygle\redo01.log'）SIZE10M,
GROUP2（'E:
\oracle\oradata\eygle\redo02.log'）SIZE10M,
GROUP3（'E:
\oracle\oradata\eygle\redo03.log'）SIZE10M;
spooloff
exit;
以上用粗体显示的就是对我们至关重要的字符集设置。
在创建数据库的过程中，在以下界面选择你的字符集，对于简体中文平台，缺省的字符集是:
ZHS16GBK
一旦你的字符集选定了，数据库中能够存储的字符就受到了限制，所以你选择的字符集的应该可以容纳所有你将用到字符。
常见的中文字符集有:
ZHS16CGB231280CGB2312-8016-bitSimplifiedChineseMB,ASCII
ZHS16GBKGBK16-bitSimplifiedChineseMB,ASCII,UDC
其中GB2312码是中华人民共和国国家汉字信息交换用编码，全称《信息交换用汉字编码字符集--基本集》，由国家标准总局发布，1981年5月1日实施，通行于大陆。
新加坡等地也使用此编码。
GBK编码是1995年12月颁布的指导性规范。
GBK与国家标准GB2312-80信息处理交换码所对应的、事实上的内码标准兼容；同时，在字汇一级支持ISO/IEC10646-1和GB13000-1的全部中日韩（CJK）汉字（20902字）。
包含了更多的编码。
但是我们说，ZHS16GBK并非是ZHS16CGB231280的严格超集（虽然后者的汉字在前者中都存在，但是同样的编码在不同两个字符集中可能表达不同的汉字），所以在做数据库字符转换时仍然需要特别注意。
Oracle的字符集命名遵循以下命名规则:
即:
<语言><比特位数><编码>
比如:
ZHS·16·GBK
需要说明的是，有些字符集命名违背了这个规范，Oracle8/Oralce8i中的UTF-8是第一个打破这个命名规范的字符集。
我们可以看到一类字符集以AL开头，如:
AL16UTF16
其中AL代表ALL,指适用于所有语言（AllLanguages），按照这个标准当年UTF-8本应被命名为AL24UTF8。
字符集的更改
数据库创建以后，如果需要修改字符集，通常需要重建数据库，通过导入导出的方式来转换。
我们也可以通过以下方式更改
ALTERDATABASECHARACTERSET
注意:
修改数据库字符集时必须谨慎，修改之前一定要为数据库备份。
由于不能回退这项操作,因此可能会造成数据丢失或者损坏。
这是最简单的转换字符集的方式，但并不总是有效。
这个命令在Oracle8时被引入Oracle,这个操作在本质上并不转换任何数据库字符，只是简单的更新数据库中所有跟字符集相关的信息。
这意味着，你只能在新字符集是旧字符集严格超集的情况下使用这种方式转换。
所谓超集是指:
当前字符集中的每一个字符在新字符集中都可以表示，并使用同样的代码点
比如很多字符集都是US7ASCII的严格超集。
如果不是超集，将获得以下错误:
SQL>ALTERDATABASECHARACTERSETZHS16CGB231280;
ALTERDATABASECHARACTERSETZHS16CGB231280
*
ERRORatline1:
ORA-12712:
newcharactersetmustbeasupersetofoldcharacterset
下面我们来看一个测试（以下测试在Oracle9.2.0下进行，Oracle9i较Oracle8i在编码方面有较大改变，在Oracle8i中，测试结果可能略有不同）:
SQL>selectname,value$fromprops$wherenamelike'%NLS%';
NAMEVALUE$
------------------------------------------------------------
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETUS7ASCII
NLS_CALENDARGREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
……………….
NLS_NCHAR_CHARACTERSETAL16UTF16
NLS_RDBMS_VERSION9.2.0.4.0
20rowsselected.
SQL>selectname,dump（name）fromeygle.test;
NAMEDUMP（NAME）
------------------------------------------------------
测试Typ=1Len=4:
178,226,202,212
TestTyp=1Len=4:
116,101,115,116
2rowsselected.
转换字符集，数据库应该在RESTRICTED模式下进行.
c:
\>sqlplus"/assysdba"
SQL*Plus:
Release9.2.0.4.0-ProductiononSatNov110:
52:
302003
Copyright（c）1982,2002,OracleCorporation.Allrightsreserved.
Connectedto:
Oracle9iEnterpriseEditionRelease9.2.0.4.0-Production
WiththePartitioning,OracleLabelSecurity,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.4.0-Production
SQL>shutdownimmediate
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
SQL>STARTUPMOUNT;
ORACLEinstancestarted.
TotalSystemGlobalArea76619308bytes
FixedSize454188bytes
VariableSize58720256bytes
DatabaseBuffers16777216bytes
RedoBuffers667648bytes
Databasemounted.
SQL>ALTERSESSIONSETSQL_TRACE=TRUE;
Sessionaltered.
SQL>ALTERSYSTEMENABLERESTRICTEDSESSION;
Systemaltered.
SQL>ALTERSYSTEMSETJOB_QUEUE_PROCESSES=0;
Systemaltered.
SQL>ALTERSYSTEMSETAQ_TM_PROCESSES=0;
Systemaltered.
SQL>ALTERDATABASEOPEN;
Databasealtered.
SQL>setlinesize120
SQL>ALTERDATABASECHARACTERSETZHS16GBK;
ALTERDATABASECHARACTERSETZHS16GBK
*
ERRORatline1:
ORA-12721:
operationcannotexecutewhenothersessionsareactive
SQL>ALTERDATABASECHARACTERSETZHS16GBK;
ALTERDATABASECHARACTERSETZHS16GBK
*
ERRORatline1:
ORA-12716:
CannotALTERDATABASECHARACTERSETwhenCLOBdataexists
在Oracle9i中，如果数据库存在CLOB类型字段，那么就不允许对字符集进行转换
SQL>
这时候，我们可以去查看alert.log日志文件，看CLOB字段存在于哪些表上:
ALTERDATABASECHARACTERSETZHS16GBK
SYS.METASTYLESHEET（STYLESHEET）-CLOBpopulated
ORA-12716signalledduring:
ALTERDATABASECHARACTERSETZHS16GBK...
对于不同情况，Oracle提供不同的解决方案，如果是用户数据表，一般我们可以把包含CLOB字段的表导出，然后drop掉相关对象，
转换后再导入数据库；对于系统表，可以按照以下方式处理:
SQL>truncatetableMetastylesheet;
Tabletruncated.
然后可以继续进行转换！
SQL>ALTERSESSIONSETSQL_TRACE=TRUE;
Sessionaltered.
SQL>ALTERDATABASECHARACTERSETZHS16GBK;
Databasealtered.
SQL>ALTERSESSIONSETSQL_TRACE=FALSE;
Sessionaltered.
在9.2.0中，转换完成以后，可以通过运行catmet.sql脚本来重建Metastylesheet表:
SQL>@?
/rdbms/admin/catmet.sql
转换后的数据：
SQL>selectname,value$fromprops$wherenamelike'%NLS%';
NAMEVALUE$
------------------------------------------------------------
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETZHS16GBK
…..
NLS_NCHAR_CHARACTERSETAL16UTF16
NLS_RDBMS_VERSION9.2.0.4.0
20rowsselected.
SQL>select*fromeygle.test;
NAME
------------------------------
测试
test
2rowsselected.
提示:
通过设置sql_trace，我们可以跟踪很多数据库的后台操作，这个工具是DBA常用的“利器”之一。
我们简单看一下数据库更改字符集时的后台处理，我提取了主要的更新部分。
通过以下跟踪过程，我们看到数据库在更改字符集的时候，主要更新了12张数据字典表，修改了数据库的原数据，这也证实了我们以前的说法:
这个更改字符集的操作在本质上并不转换任何数据库字符，只是简单的更新数据库中所有跟字符集相关的信息。
updatecol$setcharsetid=:
1
where
charsetform=:
2
updateargument$setcharsetid=:
1
where
charsetform=:
2
updatecollection$setcharsetid=:
1
where
charsetform=:
2
updateattribute$setcharsetid=:
1
where
charsetform=:
2
updateparameter$setcharsetid=:
1
where
charsetform=:
2
updateresult$setcharsetid=:
1
where
charsetform=:
2
updatepartcol$setspare1=:
1
where
charsetform=:
2
updatesubpartcol$setspare1=:
1
where
charsetform=:
2
updateprops$setvalue$=:
1
where
name=:
2
update"SYS"."KOTAD$"setSYS_NC_ROWINFO$=:
1
where
SYS_NC_OID$=:
2
updateseq$setincrement$=:
2,minvalue=:
3,maxvalue=:
4,cycle#=:
5,order$=:
6,
cache=:
7,highwater=:
8,audit$=:
9,flags=:
10
where
obj#=:
1
updatekopm$setmetadata=:
1,length=:
2
where
name='DB_FDO'
在这里我们顺便纠正一个由来已久的错误方法.
经常可以在网上看到这样的更改字符集的方法：
1）用SYS用户名登陆ORACLE。
2）查看字符集内容
SQL>SELECT*FROMPROPS$;
3）修改字符集
SQL>updateprops$setvalue$='新字符集'wherename='NLS_CHARACTERSET'
4）COMMIT;
我们看到很多人在这个问题上遇到了惨痛的教训，使用这种方式更改字符集，如果你的value$值输入了不正确的字符集，在8i中那么你的数据库可能会无法启动，这种情况是非常严重的,有时候你必须从备份中进行恢复；如果是在9i中，可以重新启动数据库后再修改回正确的字符集。
但是我们仍然不建议使用这种方式进行任何数据库修改，这是一种极其危险的操作。
实际上当我们更新了字符集，数据库启动时会根据数据库的字符集自动的来修改控制文件的字符集，如果字符集可以识别，更新控制文件字符集等于数据库字符集；如果字符集不可识别，那么控制文件字符集更新为US7ASCII.
通过更新props$表的方式修改字符集,在Oracle7之后就不应该被使用.
以下是我的测试结果，但是严禁一切不进行备份的修改研究，即使是对测试库的。
SQL>updateprops$setvalue$='EYGLE'wherename='NLS_CHARACTERSET';
1rowupdated.
SQL>commit;
Commitcomplete.
SQL>selectname,value$fromprops$wherenamelike'%NLS%';
NAMEVALUE$
-----------------------------------------------------------------
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETEYGLE
NLS_CALENDARGREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
….
NLS_NCHAR_CHARACTERSETZHS16GBK
NLS_RDBMS_VERSION8.1.7.1.1
18rowsselected.
重新启动数据库，发现alert.log文件中记录如下操作:
MonNov0316:
11:
352003
UpdatingcharactersetincontrolfiletoUS7ASCII
Completed:
ALTERDATABASEOPEN
启动数据库后恢复字符集设置:
SQL>updateprops$setvalue$='ZHS16GBK'wherename='NLS_CHARACTERSET';
1rowupdated.
SQL>commit;
Commitcomplete.
SQL>selectname,value$fromprops$wherenamelike'%NLS%';
NAMEVALUE$
-----------------------------------------------------------------
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
NLS_ISO_CURRENCYAMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CHARACTERSETZHS16GBK
NLS_CALENDARGREGORIAN
NLS_DATE_FORMATDD-MON-RR
NLS_DATE_LANGUAGEAMERICAN
………
NLS_COMPBINARY
NLS_NCHAR_CHARACTERSETZHS16GBK
NLS_RDBMS_VERSION8.1.7.1.1
18rowsselected.
重新启动数据库后，发现控制文件的字符集被更新:
Mon
升级会员 