Oracle XE 中的字符集的变换-CSDN博客

转载自：http://www.eygle.com/index-ha.htm

Oracle XE 中的字符集的变换（转载＋新增）

Oracle 10g Express Edition是Oracle专门为小型用户提供的免费版本。Oracle XE十分小巧，安装简单，可供第三方软件开发商部署较小的应用。
　　不过Oracle XE目前的beta2缺省安装的字符集是WE8MSWIN1252，不是中文字符集，并且不能通过直接运行 alter database character set ZHS16GBK ; 来修改，因为ZHS16GBK不是缺省字符集的超集。过去流传很广的直接修改sys用户下的PROPS$表的方法,也会给字符集的变更留下很多潜在的问题.
　　不过在安装完Oracle XE后，可以在sqlplus(即Oracle XE的run SQL command line)中，进行如下的操作来修改字符集：
connect system/oracle9i as sysdba
shutdown immediate
startup mount
alter system enable restricted session ;
alter system set JOB_QUEUE_PROCESSES=0;
alter system set AQ_TM_PROCESSES=0;
alter database open ;
alter database character set internal_use ZHS16GBK ;
shutdown immediate
startup

或者改成： SIMPLIFIED CHINESE_CHINA.AL32UTF8

注意：这个时候还需要通过Regedit修改客户端的字符集。

并且，Oracle Home已经由于字符集的变换用不了，全是乱码。

不过，凑合能用就行。

我们又知道，通过ALTER DATABASE CHARACTER SET更改字符集虽然安全可靠，但是有严格的子集和超集的约束，实际上我们很少能够
用到这种方法。

实际上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
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Tue Oct 19 16:27:07 2004
alter database character set INTERNAL_USE ZHS16CGB231280
Updating character set in controlfile to ZHS16CGB231280
Tue Oct 19 16:27:15 2004
Thread 1 advanced to log sequence 118
  Current log# 2 seq# 118 mem# 0: /opt/oracle/oradata/primary/redo02.log
Tue Oct 19 16:27:15 2004
ARC0: Evaluating archive   log 3 thread 1 sequence 117
ARC0: Beginning to archive log 3 thread 1 sequence 117
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/primary/archive/1_117.dbf'
ARC0: Completed archiving  log 3 thread 1 sequence 117
Tue Oct 19 16:27:20 2004
Completed: alter database character set INTERNAL_USE ZHS16CGB231280
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 1
Tue Oct 19 16:29:06 2004
ALTER DATABASE CLOSE NORMAL
...

格式化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

此处生成的日志你可以在这里下载(供参考):

http://www.eygle.com/special/primary_ora_13730.zip
http://www.eygle.com/special/primary_ora_13730.tkf.log

我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的，也就是说INTERNAL_USE提供的帮助就是使
Oracle数据库绕过了子集与超集的校验.
这一方法在某些方面是有用处的，比如测试；应用于产品环境大家应该格外小心，除了你以外，没有人会为此带来的后果负责: