SQL>ALTERDATABASECHARACTERSET ZHS16CGB231280; ALTERDATABASECHARACTERSET ZHS16CGB231280; * ERROR at line 1: ORA-12712: new characterset must be a superset of old characterset
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=1Len=4: 178,226,202,212 Test Typ=1Len=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 110:52:302003 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 SQL>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. 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>ALTERDATABASEOPEN; Database altered. SQL>set linesize 120 SQL>ALTERDATABASECHARACTERSET ZHS16GBK; ALTERDATABASECHARACTERSET ZHS16GBK * ERROR at line 1: ORA-12721: operation cannot executewhen other sessions are active SQL>ALTERDATABASECHARACTERSET ZHS16GBK; ALTERDATABASECHARACTERSET ZHS16GBK * ERROR at line 1: ORA-12716: Cannot ALTERDATABASECHARACTERSETwhen CLOB data exists 在Oracle9i中,如果数据库存在CLOB类型字段,那么就不允许对字符集进行转换 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 20 rows selected. SQL>select*from eygle.test; NAME ------------------------------ 测试 test 2 rows selected.
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'
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 0316:11:352003 Updating charactersetin controlfile to US7ASCII Completed: ALTERDATABASEOPEN 启动数据库后恢复字符集设置: 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 0316:21:412003 Updating charactersetin controlfile to ZHS16GBK Completed: ALTERDATABASEOPEN
理解了字符集调整的内部操作以后,我们可以轻易的指出,以上的方法是不正确的,通过前面 ” ALTER DATABASE CHARACTER SET” 方式更改字 符集时,Oracle至少需要更改12张数据字典表,而这种直接更新props$表的方式只完成了其中十二分之一的工作,潜在的完整性隐患是可想而知的。