转http://www.eygle.com/archives/2004/09/nls_character_set_07.html
实际上Oracle还存在另外一种更改字符集的方式.
如果你注意过的话,在Oracle的alert<sid>.log文件中,你可能看到过这样的日志信息:
alter database character set INTERNAL_CONVERT ZHS16GBK Updating character set in controlfile to ZHS16GBK SYS.SNAP$ (REL_QUERY) - CLOB representation altered SYS.METASTYLESHEET (STYLESHEET) - CLOB representation altered SYS.EXTERNAL_TAB$ (PARAM_CLOB) - CLOB representation altered XDB.XDB$RESOURCE (SYS_NC00027$) - CLOB representation altered ODM.ODM_PMML_DTD (DTD) - CLOB representation altered OE.WAREHOUSES (SYS_NC00003$) - CLOB representation altered PM.ONLINE_MEDIA (SYS_NC00042$) - CLOB representation altered PM.ONLINE_MEDIA (SYS_NC00062$) - CLOB representation altered PM.ONLINE_MEDIA (PRODUCT_TEXT) - CLOB representation altered PM.ONLINE_MEDIA (SYS_NC00080$) - CLOB representation altered PM.PRINT_MEDIA (AD_SOURCETEXT) - CLOB representation altered PM.PRINT_MEDIA (AD_FINALTEXT) - CLOB representation altered Completed: alter database character set INTERNAL_CONVERT ZHS1 |
在这里面,我们看到这样一条重要的,Oracle非公开的命令:
alter database character set INTERNAL_CONVERT/ INTERNAL_USE ZHS16GBK |
这个命令是当你选择了使用典型方式创建了种子数据库以后,Oracle会根据你选择的字符集设置,把当前种子数据库的字符集更改为期望字符
集,这就是这条命令的作用.
在使用这个命令时,Oracle会跳过所有子集及超集的检查,在任意字符集之间进行强制转换,所以,使用这个命令时你必须十分小心,你必须
清楚这一操作会带来的风险.
我们之前讲过的内容仍然有效,你可以使用csscan扫描整个数据库,如果在转换的字符集之间确认没有严重的数据损坏,或者你可以使用有效
的方式更改,你就可以使用这种方式进行转换.
我们来看一下具体的操作过程及Oracle的内部操作:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. 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> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> alter database character set INTERNAL_USE ZHS16CGB231280 Database altered. SQL> |
这是alert.log文件中的记录信息:
Tue Oct 19 16:26:30 2004 |
格式化10046跟踪文件,得到以下信息(摘要):
alter session set events '10046 trace name context forever,level 12' alter database character set INTERNAL_USE ZHS16CGB231280 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 4.88 6.04 910 16825 18099 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 4.88 6.04 910 16825 18099 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ control file sequential read 4 0.00 0.00 control file parallel write 2 0.05 0.08 log file sync 2 0.08 0.08 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 18.06 18.06 ******************************************************************************** .... 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' .... ALTER DATABASE CLOSE NORMAL |
此处生成的日志你可以在这里下载(供参考):