第3章 Oracle的字符集（3.5-3.6）
Oracle全球支持(即Globalization Support)允许我们使用本地语言和格式来存储和检索数据。通过全球支持，Oracle可以支持多种语言及字符集，得以展示数据库的强大魅力。这篇介绍第三章的3.5-3.6节：字符集的更改、识别导出文件的字符集。
3.5 字符集的更改
数据库创建以后，如果需要修改字符集，通常需要重建数据库，通过导入导出的方式来转换。也可以通过以下方式更改：
ALTER DATABASE CHARACTER SET
注意：修改数据库字符集时必须谨慎，修改之前一定要为数据库备份。由于不能回退这项操作，因此可能会造成数据丢失或者损坏。
这是最简单的转换字符集的方式，但并不总是有效。这个命令在Oracle 8时被引入Oracle，这个操作在本质上并不转换任何数据库字符，只是简单地更新数据库中所有跟字符集相关的信息。
这意味着，只能在新字符集是旧字符集严格超集的情况下使用这种方式转换。所谓超集是指当前字符集中的每一个字符在新字符集中都可以表示，并使用同样的代码点，比如很多字符集都是US7ASCII的严格超集。
如果不是超集，将获得以下错误：
SQL> ALTER DATABASE CHARACTER SET ZHS16CGB231280; ALTER DATABASE CHARACTER SET ZHS16CGB231280 * ERROR at line 1: ORA-12712: new character set must be a superset of old character set
下面来看一个测试（以下测试在Oracle 9.2.0下进行，Oracle 9i较Oracle 8i在编码方面有较大改变，在Oracle 8i中，测试结果可能略有不同）：
SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET US7ASCII NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN ………………. NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.4.0 20 rows selected. SQL> select name,dump(name) from eygle.test; NAME DUMP(NAME) ------------------------------------------------------ 测试 Typ=1 Len=4: 178,226,202,212 Test Typ=1 Len=4: 116,101,115,116 2 rows selected.
转换字符集，数据库应该在RESTRICTED模式下进行：
SQL> shutdown immediate SQL> STARTUP MOUNT; ORACLE instance started. Database mounted. SQL> ALTER SESSION SET SQL_TRACE=TRUE; Session altered. SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; System altered. SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; System altered. SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; System altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> set linesize 120 SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-12721: operation cannot execute when other sessions are active SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists
在Oracle 9i中，如果数据库存在CLOB类型字段，那么就不允许对字符集进行转换，这时可以去查看alert_<sid>.log日志文件，看CLOB字段存在于哪些表上：
ALTER DATABASE CHARACTER SET ZHS16GBK SYS.METASTYLESHEET (STYLESHEET) - CLOB populated ORA-12716 signalled during: ALTER DATABASE CHARACTER SET ZHS16GBK...
对于不同情况，Oracle提供不同的解决方案，如果是用户数据表，一般我们可以把包含CLOB字段的表导出，然后drop掉相关对象，转换后再导入数据库；对于以上提示的系统表，可以按照以下方式处理：
SQL> truncate table Metastylesheet; Table truncated.
然后可以继续进行转换：
SQL> ALTER SESSION SET SQL_TRACE=TRUE; Session altered. SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; Database altered. SQL> ALTER SESSION SET SQL_TRACE=FALSE; Session altered.
在Oracle 9.2.0中，转换完成以后，可以通过运行catmet.sql脚本来重建Metastylesheet表：
SQL> @?/rdbms/admin/catmet.sql
转换后的数据：
SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK ….. NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.4.0 SQL> select * from eygle.test; NAME ------------------------------ 测试 test
提示：通过设置sql_trace，可以跟踪很多数据库的后台操作，这个工具是DBA常用的“利器”之一。
简单看一下数据库更改字符集时的后台处理，这里提取了主要的更新部分。
通过以下跟踪过程，可以看到数据库在更改字符集的时候，主要更新了12张数据字典表，修改了数据库的原数据，这也证实了我们以前的说法：这个更改字符集的操作在本质上并不转换任何数据库字符，只是简单地更新数据库中所有跟字符集相关的信息。
update col$ set charsetid = :1 where charsetform = :2 update argument$ set charsetid = :1 where charsetform = :2 update collection$ set charsetid = :1 where charsetform = :2 update attribute$ set charsetid = :1 where charsetform = :2 update parameter$ set charsetid = :1 where charsetform = :2 update result$ set charsetid = :1 where charsetform = :2 update partcol$ set spare1 = :1 where charsetform = :2 update subpartcol$ set spare1 = :1 where charsetform = :2 update props$ set value$ = :1 where name = :2 update "SYS"."KOTAD$" set SYS_NC_ROWINFO$ = :1 where SYS_NC_OID$ = :2 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 update kopm$ set metadata = :1, length = :2 where name='DB_FDO'
在这里顺便纠正一个由来已久的错误方法，经常可以在网上看到这样的更改字符集的方法，这种方法应该被忘记，绝对不应该被采用：
（1）用SYS用户名登录ORACLE。
（2）查看字符集内容 SELECT * FROM PROPS$;
（3）修改字符集 update props$ set value$='新字符集' where name='NLS_CHARACTERSET'
（4） COMMIT;
很多人在这个问题上遇到了惨痛的教训。使用这种方式更改字符集，如果你的value$值输入了不正确的字符集，那么在Oracle 8i中你的数据库就可能会无法启动。这种情况是非常严重的，有时候你必须从备份中进行恢复；如果是在Oracle 9i中，可以重新启动数据库后再修改回正确的字符集。实际上当更新了字符集，数据库启动时会根据数据库的字符集自动地来修改控制文件的字符集，如果字符集可以识别，更新控制文件字符集等于数据库字符集；如果字符集不可识别，那么控制文件字符集更新为US7ASCII。
以下是我的测试结果，但是严禁一切不备份的修改研究，即使是对测试库的。
SQL> update props$ set value$='EYGLE' where name='NLS_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ----------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_CHARACTERSET EYGLE NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN …. NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.1.1 18 rows selected.
重新启动数据库，发现alert.log文件中记录如下操作：
Mon Nov 03 16:11:35 2003 Updating character set in controlfile to US7ASCII Completed: ALTER DATABASE OPEN
启动数据库后恢复字符集设置：
SQL> update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ----------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_DATE_LANGUAGE AMERICAN ……… NLS_COMP BINARY NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.1.1 18 rows selected.
重新启动数据库后，发现控制文件的字符集被更新：
Mon Nov 03 16:21:41 2003 Updating character set in controlfile to ZHS16GBK Completed: ALTER DATABASE OPEN
理解了字符集调整的内部操作以后，我们可以轻易地指出，以上方法是不正确的，通过前面“ALTER DATABASE CHARACTER SET”方式更改字符集时，Oracle至少需要更改12张数据字典表，而这种直接更新props$表的方式只完成了其中十二分之一的工作，潜在的完整性隐患是可想而知的。
所以，更改字符集尽量要使用正常的途径。
3.6 识别导出文件的字符集
在传统的EXP导出文件中，记录着导出使用的字符集id，通过查看导出文件头的第2、3个字节，我们可以找到十六进制表示的字符集ID，在Windows上，可以使用UltraEdit等工具打开dmp文件，查看其导出字符集，如图3-6所示。
图3-6 导出字符集
在UNIX环境上，可以通过以下命令来查看（以下范例来自Solaris平台），如图3-7所示。
cat expdat.dmp | od -x | head
图3-7 查看字符集
需要注意的是，在不同平台，以上命令的输出可能有所不同，比如在Linux平台：
[oracle@jumper oracle]$ cat a.dmp |od -x |head -2 0000000 0303 4554 5058 524f 3a54 3156 2e30 3230 0000020 302e 0a31 4544 4759 454c 520a 4154 4c42
这是由于Solaris和Linux的字节序是不同的，Solaris是Big-Endians，Linux是Little-Endians，所以Linux上的输出通过交换可以得到：
0303 5445 5850 4f52 ….
字符集和Solaris上是一致的，所以也可以通过od的参数设置显示格式：
[oracle@jumper oracle]$ cat a.dmp|od -t x1|head -2 0000000 03 03 54 45 58 50 4f 52 54 3a 56 31 30 2e 30 32 0000020 2e 30 31 0a 44 45 59 47 4c 45 0a 52 54 41 42 4c
Oracle提供标准函数，对字符集名称及ID进行转换：
SQL> select nls_charset_id('ZHS16GBK') from dual;
NLS_CHARSET_ID('ZHS16GBK')
--------------------------
852
SQL> select nls_charset_name(852) from dual;
NLS_CHAR
--------
ZHS16GBK十进制转换十六进制，即可获得字符集的编码:
SQL> select to_char('852','xxxx') from dual;
TO_CH
-----
354对应 图3-6或图3-7 第2、3字节，就知道该导出文件字符集为ZHS16GBk。
查询数据库中有效的字符集可以使用以下脚本：
col nls_charset_id for 9999 col nls_charset_name for a30 col hex_id for a20 select nls_charset_id(value) nls_charset_id, value nls_charset_name,to_char(nls_charset_id(value), 'xxxx') hex_id from v$nls_valid_values where parameter = 'CHARACTERSET' order by nls_charset_id(value)；
输出样例如下：
NLS_CHARSET_ID NLS_CHARSET_NAME HEX_ID -------------- ------------------------------ ------------- 1 US7ASCII 1 2 WE8DEC 2 ................... 850 ZHS16CGB231280 352 851 ZHS16MACCGB231280 353 852 ZHS16GBK 354 853 ZHS16DBCS 355 ………………… 868 ZHT16HKSCS 364 870 AL24UTFFSS 366 871 UTF8 367
而对于Oracle 10g的expdp导出文件，一切则要简单得多，在expdp的导出文件开始部分，Oracle以XML格式记录了数据的字符集信息，以下是一个单表导出文件的头信息。
在文件开始部分就记录了数据库的字符集、国家字符集及时区等信息，在表字段的属性部分也记录了字符集ID：
<?xml version="1.0"?> <ROWSET><ROW> <STRMTABLE_T> <VERS_MAJOR>1</VERS_MAJOR> <VERS_MINOR>0 </VERS_MINOR> <VERS_DPAPI>3</VERS_DPAPI> <ENDIANNESS>2</ENDIANNESS> <CHARSET>ZHS16GBK</CHARSET> <NCHARSET>AL16UTF16</NCHARSET> <DBTIMEZONE>+08:00</DBTIMEZONE> <FDO>0000006001240F050B0C0....</FDO> <OBJ_NUM>79220</OBJ_NUM> <OWNER_NAME>EYGLEE</OWNER_NAME> <NAME>TEST</NAME> <PROPERTY>536870912</PROPERTY> <COL_LIST> <COL_LIST_ITEM> <OBJ_NUM>79220</OBJ_NUM> <COL_NUM>1</COL_NUM> <INTCOL_NUM>1</INTCOL_NUM> <SEGCOL_NUM>1</SEGCOL_NUM> <PROPERTY>0</PROPERTY> <NAME>NAME</NAME> <TYPE_NUM>1</TYPE_NUM> <LENGTH>20</LENGTH> <NOT_NULL>0</NOT_NULL> <CHARSETID>852</CHARSETID> <CHARSETFORM>1</CHARSETFORM> <CHARLENGTH>20</CHARLENGTH> </COL_LIST_ITEM> </COL_LIST> </STRMTABLE_T> </ROW></ROWSET>
对于传统的DMP导出文件，在很多时候，当进行导入操作时，已经离开了源数据库，这时如果目标数据库的字符集和导出文件不一致，多半就需要进行特殊处理进行转换。最常见的转换发生在从US7ASCII到ZHS16GBK之间。
由于很多数据库最初以US7ASCII字符集存储中文，单纯通过导出导入是无法完成字符集转换的。对于这种情况，可以通过设置导出字符集US7ASCII，原样导出数据；导出后修改导出文件的第二、三字符，修改0001为0354，这样就可以将US7ASCII字符集的数据正确导入到ZHS16GBK的数据库中。
如图3-8所示是一个测试例子，我们可以通过UltraEdit等工具的二进制编辑模式修改导出文件：
图3-8 修改后的字符集
修改完成之后，可以导入修改后的DMP文件：
E:\nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK E:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygle tables=test Export file created by EXPORT:V08.01.07 via conventional path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set export server uses UTF8 NCHAR character set (possible ncharset conversion) . . importing table "TEST" 2 rows imported Import terminated successfully without warnings.
通过这种方式，最终中文可以被正常导入ZSH16GBK的数据库：
E:\nls2>sqlplus eygle/eygle SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 3 17:37:23 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> select name,dump(name) from test; NAME DUMP(NAME) -------- ------------------------------------------------------------------------ 测试 Typ=1 Len=4: 178,226,202,212 Test Typ=1 Len=4: 116,101,115,116 2 rows selected.
另外一种可以尝试的方法是使用create database命令。如果导出文件使用的字符集是US7ASCII，目标数据库的字符集是ZHS16GBK，就可以使用create database的方法来修改，具体操作如下：
SQL> select * from v$nls_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET ZHS16GBK NLS_SORT BINARY ……………… 19 rows selected. SQL> create database character set us7ascii; create database character set us7ascii * ERROR at line 1: ORA-01031: insufficient privileges SQL> select * from v$nls_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET US7ASCII NLS_SORT BINARY ………….. 19 rows selected.
然后可以导入数据：
E:\nls2>set nls_lang=AMERICAN_AMERICA.US7ASCII E:\nls2>imp eygle/eygle file=Sus7ascii-Cus7ascii.dmp fromuser=eygle touser=eygle Export file created by EXPORT:V09.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . . importing table "TEST" 2 rows imported Import terminated successfully without warnings.
查询导入数据：
E:\nls2>sqlplus eygle/eygle SQL> select * from test; NAME ---------- 测试 test
当发出“create database character set us7ascii；”命令时，数据库v$nls_parameters中的字符集设置随之更改，该参数影响导入进程，更改后可以正确导入数据，重起数据库后，该设置恢复。
提示：v$nls_paraemters来源于x$nls_parameters，该动态性能视图影响导入操作；而nls_database_parameters来源于props$数据表，影响数据存储。以上的方法只应该在不得已的情况下使用，其本质是欺骗数据库，强制导入数据，但是可能会损失元数据。
