网站建设
联系我们

在10g中修改数据库字符集

发布于:2014-02-12 09:57来源:未知 作者:admin 点击:
来源:http://www.itpub.net/thread-917943-1-1.html
一般来说,数据库在创建完成后就不能修改字符集了,如要修改字符集,需要对原有数据进行导入导出后重建数据库来修改。但是也有一些例外的,如果从子集升级到超集,则可以直接修改而无需重建数据库。
子集到超集,在8I/9I中,简单的修改步骤如下:
                SHUTDOWN IMMEDIATE;
     -- make sure there is a database backup you can rely on, or create one
     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 <new_character_set>; 
     -- a alter database takes typically only a few minutes or less, 
     -- it depends on the number of columns in the database, not the 
     -- amount of data.
     SHUTDOWN;
     -- If you use Oracle8 then also do:
     STARTUP RESTRICT; 
     SHUTDOWN;

但是,以上步骤只适用于8I/9I,对于10g,oracle明确指出不能这样干了,在10g,oracle提供了一个新工具CSALER来修改字符集。

下面说明如何在10g用修改字符集。

1、当前字符集
sys@TEST> select userenv('language') from dual;                                                                                     
                                                                                                                                    
USERENV('LANGUAGE')                                                      
------------------------------------------------------                         
SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280                            

2、插入一些生僻字

sys@TEST> create table a(a varchar2(100));

表已创建。

sys@TEST> insert into a values('珮');

已创建 1 行。

sys@TEST> commit;

提交完成。

sys@TEST> select *from a;

A
--------------------


可见,ZHS16CGB231280包含的汉字太少,需要升级包含汉字更多的ZHS16GBK。ZHS16GBK是ZHS16CGB231280的超集,可以直接修改字符集。

3、关闭数据库并备份
        由于更新数据库操作不可回滚,安全起见,最好对数据库做一个全备。

4、使用csscan检查字符集转换是否可行
        csscan扫描数据库的所有数据并测试字符集转换是否可行。
        csscan有四种扫描模式:全库扫描、按用户扫描、按表扫描、按列扫描。
        因为我们这里是测试改变全库的字符集,所以要用全库扫描模式,并且要求扫描用户有DBA权限:


                                        
                                        运行csscan需要一系列的权限和表,因此需要先运行一个脚本,否则会报错:
                                        sys@TEST> @E:\oracle\ora10g\RDBMS\ADMIN\csminst.sql
                                        ......
                                        
                                        
                                        视图已创建。
                                        
                                        
                                        同义词已删除。
                                        
                                        
                                        同义词已创建。
                                        
                                        
                                        视图已创建。
                                        
                                        
                                        视图已创建。
                                        
                                        
                                        提交完成。
                                        
                                        从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options 断开
                                        
                                        运行CSSCAN扫描数据库:

                                        E:\oracle\ora10g\BIN>CSSCAN  SYSTEM/AAA FULL=y FROMCHAR=ZHS16CGB231280 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1
                                                简单说一下几个参数的含义:
                                                                                username/password :数据库用户名和口令,需要有dba权限
                                                                                FULL                                                  :是否进行全库扫描
                                                                                FROMCHAR                                  :原字符集,可以省略,默认为连接数据库的当前字符集
                                                                                TOCHAR                                                :目标字符集
                                                                                ARRAY                                                        :读取数据的缓冲区大小
                                                                                PROCESS                                                :同时启动几个进程进行扫描
                                        
                                        Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on 星期四 12月 27 18:49:55 2007
                                        
                                        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, Oracle Label Security, OLAP and Data Mining options
                                        
                                        Enumerating tables to scan...
                                        .......
                                        . process 1 scanning SYS.WRH$_LATCH_MISSES_SUMMARY[AAACUwAADAAAAzRAAA]
                                        . process 1 scanning SYSTEM.LOGMNR_COL$[AAABbGAADAAAAeZAAA]
                                        . process 1 scanning SYSTEM.LOGMNR_ATTRCOL$[AAABa/AADAAAAhpAAA]
                                        ......
                                        Creating Database Scan Summary Report...
                                        
                                        Creating Individual Exception Report...
                                        
                                        Scanner terminated successfully.
                                        
                                        默认情况下,csscan扫描结束后,会产生三个文件:scan.txt、scan.err、scan.out,分别查看这三个文件,如果没有异常,则可以往下执行了。
                                        
                                        csscan会把最近一次执行扫描的参数写入表csm$parameters中,这个表的参数非常重要,它决定着下一步进行字符集转换需要的参数,如要转成什么字符集等。
                                        sys@TEST> select * from csm$parameters;
                                        
                                        NAME                           VALUE
                                        ------------------------------ ----------------------------------------
                                        SCANNER_VERSION                5
                                        SCAN_TYPE                      ALL
                                        SCAN_CHAR                      YES
                                        TO_CHARSET_NAME                ZHS16GBK
                                        FROM_CHARSET_NAME              ZHS16CGB231280
                                        SCAN_NCHAR                     NO
                                        MAX_ARRAY_SIZE                 1024000
                                        MAX_ROWS_IN_HEAP               100
                                        NUMBER_OF_PROCESS              1
                                        SUPPRESS_ERROR_LOG_BY          -1
                                        INSERT_SUPPRESSED              NO
                                        CAPTURE_CONVERTIBLE_DATA       NO
                                        SCANNER_SCRIPT                 NO
                                        SCANNER_PRESERVE               NO
                                        MIGRATE_TO_SUPERSET            0
                                        CSLD_ENABLE                    0
                                        PREVIOUS_CHARACTER_SET         ZHS16CGB231280
                                        PREVIOUS_NCHAR_SET             AL16UTF16
                                        TIME_START                     2007-12-27 18:50:02
                                        TIME_END                       2007-12-27 18:51:35


5、运行CSALTER修改字符集
        CSALTER是10g新推出的用于修改oracle字符集的工具,它位于$ORACLE_HOME/RDBMS/ADMIN/中,其实这个工具并不神秘,它只不过是把文章开头提到的9i中修改字符集的主要步骤写成脚本,并新增一些自动检查功能,简化操作并尽量避免错误产生。
                                sys@TEST> @E:\oracle\ora10g\RDBMS\ADMIN\CSALTER.PLB
                                
                                已创建0行。
                                
                                
                                函数已创建。
                                
                                
                                函数已创建。
                                
                                
                                过程已创建。
                                
                                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
                                原值    6:     if (UPPER('&conf') <> 'Y') then
                                新值    6:     if (UPPER('y') <> 'Y') then
                                Checking data validility...
                                begin converting system objects
                                
                                PL/SQL 过程已成功完成。
                                
                                Alter the database character set...
                                CSALTER operation completed, please restart database
                                
                                PL/SQL 过程已成功完成。
                                
                                
                                已删除0行。
                                
                                
                                函数已删除。
                                
                                
                                函数已删除。
                                
                                
                                过程已删除。

这里注意到,oracle并没有要求我们输入参数来执行要把当前数据库字符集转换为什么字符集,实际上,CSALERT读取csm$parameters中设定的参数值来进行字符集转换。
这个操作会很快完成,因为csalter并没有修改实际数据,只是把数据字典中的元数据修改一下而已。
从后台日志可以看出这个过程执行的操作:
                                Thu Dec 27 22:25:43 2007
                                ALTER SYSTEM enable restricted session;
                                MMNL started with pid=11, OS id=3516
                                Thu Dec 27 22:25:43 2007
                                ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
                                Thu Dec 27 22:25:43 2007
                                ALTER SYSTEM SET aq_tm_processes=0 SCOPE=BOTH;
                                Thu Dec 27 22:25:43 2007
                                alter database character set internal_use ZHS16GBK
                                Thu Dec 27 22:25:46 2007
                                Updating character set in controlfile to ZHS16GBK
                                Synchronizing connection with database character set information
                                Refreshing type attributes with new character set information
                                Completed: alter database character set internal_use ZHS16GBK

6、重启数据库
                        sys@TEST> shutdown immediate
                        数据库已经关闭。
                        已经卸载数据库。
                        ORACLE 例程已经关闭。
                        sys@TEST> startup
                        ORACLE 例程已经启动。
                        
                        Total System Global Area  603979776 bytes
                        Fixed Size                  1250380 bytes
                        Variable Size             159386548 bytes
                        Database Buffers          436207616 bytes
                        Redo Buffers                7135232 bytes
                        数据库装载完毕。
                        数据库已经打开。

7、验证
                        sys@TEST> select userenv('language') from dual;
                        
                        USERENV('LANGUAGE')
                        ----------------------------------------------------
                        SIMPLIFIED CHINESE_CHINA.ZHS16GBK

--可见,数据库字符集已经变为ZHS16GBK了。
再查原来是乱码的数据:
                        sys@TEST> select * from a;
                        
                        A
                        ----------
                        ?

查询结果是乱码,这也就是说对于原来库中已经是乱码的数据,修改字符集是无能为力的。

我们再看看此时是否可以插入原来是乱码的汉字:

                        sys@TEST> insert into a values('珮');
                        
                        已创建 1 行。
                        
                        sys@TEST> select * from a;
                        
                        A
                        ----------
                        ?
                        珮

原来是乱码的汉字现在可以正常插入和显示,字符集修改成功。

 
 
 
tag标签:
------分隔线----------------------------
------分隔线----------------------------