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

cnhtm發表於2009-12-24
Oracle

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@]

安裝csscan

oracle@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--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22049049/viewspace-1029939/，如需轉載，請註明出處，否則將追究法律責任。

相關文章