字符集问题的初步探讨(三)
字符集问题的初步探讨(三) link:
http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm
2． 字符集的更改
ALTER DATABASE CHARACTER SET
注意:修改数据库字符集时必须谨慎，修改之前一定要为数据库备份。由于不能回退这项操作,因此可能会造成数据丢失或者损坏。
这是最简单的转换字符集的方式，但并不总是有效。
这个命令在Oracle8时被引入Oracle,这个操作在本质上并不转换任何数据库字符，只是简单的更新数据库中所有跟字符集相关的信息。
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
下面我们来看一个测试(以下测试在Oracle9.2.0下进行，Oracle9i较Oracle8i在编码方面有较大改变，在Oracle8i中，测试结果可能略有不同):
SQL> select name,value$ from props$ where name like '%NLS%';NAME VALUE$------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET US7ASCIINLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICAN……………….NLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 9.2.0.4.020 rows selected.SQL> select name,dump(name) from eygle.test;NAME DUMP(NAME)------------------------------------------------------测试 Typ=1 Len=4: 178,226,202,212Test Typ=1 Len=4: 116,101,115,1162 rows selected.
转换字符集，数据库应该在RESTRICTED模式下进行.
c:\>sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 10:52: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> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP MOUNT;ORACLE instance started.Total System Global Area 76619308 bytesFixed Size 454188 bytesVariable Size 58720256 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase 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 120SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;ALTER DATABASE CHARACTER SET ZHS16GBK*ERROR at line 1:ORA-12721: operation cannot execute when other sessions are activeSQL> 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在Oracle9i中，如果数据库存在CLOB类型字段，那么就不允许对字符集进行转换SQL>
ALTER DATABASE CHARACTER SET ZHS16GBK
SYS.METASTYLESHEET (STYLESHEET) - CLOB populated
ORA-12716 signalled during: ALTER DATABASE CHARACTER SET ZHS16GBK...
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.
SQL> @?/rdbms/admin/catmet.sql
SQL> select name,value$ from props$ where name like '%NLS%';NAME VALUE$------------------------------ ------------------------------NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET ZHS16GBK…..NLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 9.2.0.4.020 rows selected.SQL> select * from eygle.test;NAME------------------------------测试test2 rows selected.
这个更改字符集的操作在本质上并不转换任何数据库字符，只是简单的更新数据库中所有跟字符集相关的信息。
update props$ set value$ = :1 where name = :2update "SYS"."KOTAD$" set SYS_NC_ROWINFO$ = :1 where SYS_NC_OID$ = :2update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1update kopm$ set metadata = :1, length = :2 where name='DB_FDO'
1）用SYS用户名登陆ORACLE。
2）查看字符集内容
SQL>SELECT * FROM PROPS$;
3）修改字符集
SQL> update props$ set value$='新字符集' where name='NLS_CHARACTERSET'
4) COMMIT;
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 AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET EYGLENLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICAN….NLS_NCHAR_CHARACTERSET ZHS16GBKNLS_RDBMS_VERSION 8.1.7.1.118 rows selected.重新启动数据库，发现alert.log文件中记录如下操作:Mon Nov 03 16:11:35 2003Updating character set in controlfile to US7ASCIICompleted: 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 AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET ZHS16GBKNLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICAN………NLS_COMP BINARYNLS_NCHAR_CHARACTERSET ZHS16GBKNLS_RDBMS_VERSION 8.1.7.1.118 rows selected.重新启动数据库后，发现控制文件的字符集被更新:Mon Nov 03 16:21:41 2003Updating character set in controlfile to ZHS16GBKCompleted: ALTER DATABASE OPEN
符集时，Oracle至少需要更改12张数据字典表，而这种直接更新props$表的方式只完成了其中十二分之一的工作，潜在的完整性隐患是可想而知的。
所以，更改字符集尽量要使用正常的途径。