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
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模式下进行.
c:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 10:52:30 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Total System Global Area 76619308 bytes
Fixed Size 454188 bytes
Variable Size 58720256 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
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
在Oracle9i中,如果数据库存在CLOB类型字段,那么就不允许对字符集进行转换
SQL>
这时候,我们可以去查看alert<sid>.log日志文件,看CLOB字段存在于哪些表上:
ALTER DATABASE CHARACTER SET ZHS16GBK SYS.METASTYLESHEET (STYLESHEET) - CLOB populated
ORA-12716 signalled during: ALTER DATABASE CHARACTER SET ZHS16GBK...
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_NUMERIC_CHARACTERS .,
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_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
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$表的方式只完成了其中十二分之一的工作,潜在的完整性隐患是可想而知的。