oracle 10修改字符集,Oracle 10中修改字符集(character set)

Oracle 10.2.0.3数据库原来的字符集是ZHS16GBK，为了支持更多的汉字，需要修改为ZHS32GB18030。

我首先想到原来在9i上修改字符集的方法，过程如下：

sys@CNHTM> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@CNHTM> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218292 bytes

Variable Size 79694092 bytes

Database Buffers 79691776 bytes

Redo Buffers 7168000 bytes

Database mounted.

sys@CNHTM> alter system set job_queue_processes=0;

System altered.

sys@CNHTM> alter system set aq_tm_processes=0;

System altered.

sys@CNHTM> alter system enable restricted session;

System altered.

sys@CNHTM> alter database open;

Database altered.

sys@CNHTM> alter database character set ZHS32GB18030;

alter database character set ZHS32GB18030

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

晕啊，ZHS32GB18030字符集明明是ZHS16GBK的超集，查metalink才知道，原来在Oracle 10G中，修改字符集需要用csalter配合csscan完成。过程如下：[@more@]

安装csscanoracle@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 23 19:58:21 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

sys@CNHTM> set termout on

sys@CNHTM> set echo on

sys@CNHTM> spool /tmp/csminst.log

sys@CNHTM> @?/rdbms/admin/csminst.sql

sys@CNHTM> rem

sys@CNHTM> rem NAME

sys@CNHTM> rem csminst.sql

... 省略n行 ...

sys@CNHTM> rem *****************************************************************

sys@CNHTM> rem CSMV$EXTABLES lists all distinct objects to be scaned

sys@CNHTM> rem *****************************************************************

sys@CNHTM> create or replace view csmig.csmv$extables

2 (obj#, usr#, property)

3 as

4 select distinct(obj#), usr#, property

5 from csm$extables where property=0;

View created.

sys@CNHTM> /

View created.

sys@CNHTM> commit

2 /

Commit complete.

sys@CNHTM> exit;

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

安装后会自动退出plsql，可以检查/tmp/csminst.log文件，查看安装的详细日志。

测试csscan是否安装成功oracle@oracle[/home/oracle]> csscan table=sys.sql_version$ fromchar=zhs16gbk tochar=zhs32gb18030 log=instchkc capture=n process=1 array=1024000

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Dec 23 20:15:30 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba (这里输入用户名密码)

Password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Enumerating table to scan...

. process 1 scanning SYS.SQL_VERSION$[AAAAIDAABAAAA/xAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

如果显示Scanner terminated successfully.证明csscan安装成功，会生成如下三个文件。把这三个文件删除。oracle@oracle[/home/oracle]> ls -l instchkc*

-rw-r--r-- 1 oracle oinstall 1357 Dec 23 20:16 instchkc.err

-rw-r--r-- 1 oracle oinstall 504 Dec 23 20:16 instchkc.out

-rw-r--r-- 1 oracle oinstall 5481 Dec 23 20:16 instchkc.txt

开始正式运行csscan，下一步的csalter过程依赖这一步的运行结果。oracle@oracle[/home/oracle]> csscan full=y tochar=zhs32gb18030 array=1024000 process=2

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Wed Dec 23 20:21:14 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / as sysdba (这里输入用户名密码)

Password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]

. process 2 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]

...省略n行...

. process 2 scanning SYS.WRH$_INST_CACHE_TRANSFER[AAAMgoAADAAABNhAAA]

. process 1 scanning SYSTEM.LOGMNRC_GTLO[AAABa0AADAAAAZhAAA]

. process 2 scanning SYS.WRH$_DLM_MISC[AAAMhlAADAAAA5RAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

运行csalter，修改字符集oracle@oracle[/home/oracle]> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 23 20:34:12 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

sys@CNHTM> @?/rdbms/admin/csalter.plb

4 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.

Please ensure you have a full backup before initiating this procedure.

Would you like to proceed (Y/N)?y

old 6: if (UPPER('&conf') <> 'Y') then

new 6: if (UPPER('y') <> 'Y') then

Checking data validility...

begin converting system objects

PL/SQL procedure successfully completed.

Alter the database character set...

CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.

4 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

重启数据库sys@CNHTM> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@CNHTM> startup

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1218292 bytes

Variable Size 71305484 bytes

Database Buffers 88080384 bytes

Redo Buffers 7168000 bytes

Database mounted.

Database opened.

检查字符集sys@CNHTM> select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.ZHS32GB18030

结果显示，修改成功

--end--