ITPub博客

首页 > 数据库 > Oracle > Oracle字符集问题总结

Oracle字符集问题总结

原创 Oracle 作者:rattbell 时间:2006-09-05 10:01:03 0 删除 编辑

Oracle字符集问题总结

/*
# =========Oracle Character Convert=====================
# Copyright (c) 1995-2003 by Rattbell.cheng
# Ver1.7 10:50 2006-4-20
# All Rights Reserved.
#
# Email: Rattbell@163.com,
# Msn: Rattbell@hotmail.com
# ======================================================================
*/

set echo off
set head off

prompt
prompt Checking USER based on login;
show user

spool charact_set.sql
set feedback off
set verify off
set linesize 250
set trimspool off

prompt select 'Con:Schema Planed to be convert :'||user from dual;;
prompt prompt Con:Press Enter to start, Otherwise,Press Ctrl-C to exit. ;
prompt set echo on
prompt pause

Select 'update ' ||t.table_name
||' set '
||c.COLUMN_NAME|| '=CONVERT( CONVERT(' ||c.COLUMN_NAME||',''we8iso8859p1'',''utf8''), ''utf8'',''zhs16cgb231280''); '
||chr(10)||'commit;'
from user_tables T,USER_TAB_COLUMNS C
where t.table_name=C.table_name
and data_type='VARCHAR2' ;

prompt prompt Con:Charset Convert finished;

spool off

#select chr(32)||chr(32)||t.table_name, count(c.COLUMN_NAME) Column_num
from user_tables T,USER_TAB_COLUMNS C
where t.table_name=C.table_name
group by t.table_name;
#select 'Total table Number:'||count(*) tab_num from user_tables;

set feedback on
set sqlprompt .

@@charact_set.sql

set linesize 80
set verify on
set trimspool on
set head on

exit;

#=======================================
#以上Script 用于强行转换字符集至Utf8;
# 原始DB字符集为we8iso8859p1,
# 其中存储的中文字符乱码将被纠正;
#=======================================

[@more@]

Oracle字符集问题总结

一、什么是oracle字符集

Oracle字符集是一个字节数据的解释的符号集合,有大小之分,有相互的包容关系。

ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储,处理,检索数据。它使数据库工具,错误消息,排序次序,日期,时间,货币,数字,和日历自动适应本地化语言和平台。

  影响oracle数据库字符集最重要的参数是NLS_LANG参数。它的格式如下:

   NLS_LANG = language_territory.charset

:AMERICAN _ AMERICA. ZHS16GBK它有三个组成部分(语言、地域和字符集)每个成分控制了NLS子集的特性。其中:

Language 指定服务器消息的语言

territory 指定服务器的日期和数字格式

charset 指定字符集。

从NLS_LANG的组成我们可以看出,真正影响数据库字符集的其实是第三部分。所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据,前面影响的只是提示信息是中文还是英文。

二、如何查询Oracle的字符集

   很多人都碰到过因为字符集不同而使数据导入失败的情况。这涉及三方面的字符集,一是oracel server端的字符集,二是oracle client端的字符集;三是dmp文件的字符集。在做数据导入的时候,需要这三个字符集都一致才能正确导入。

 1、查询oracle server端的字符集

有很多种方法可以查出oracle server端的字符集,比较直观的查询方法是以下这种:

SQL>select * from PROPS$ where name='NLS_CHARACTERSET'

SQL>select userenv(language) from dual;

 结果类似如下:AMERICAN _ AMERICA. ZHS16GBK

 2、如何查询dmp文件的字符集

用exp工具导出的dmp文件也包含了字符集信息,文件的第2、3个字节记录了dmp文件的字符集。

如果dmp文件不大,比如只有几M或几十M,可以用UltraEdit打开(16进制方式),看第2、3个字

节的内容,如0354,然后用以下SQL查出它对应的字符集:

  SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;

   ZHS16GBK

如果dmp文件很大,比如有2G以上(这也是最常见的情况),用文本编辑器打开很慢或者完全打

不开,可以用以下命令(在unix主机上):

  cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

 然后用上述SQL也可以得到它对应的字符集。

 3、查询oracle client端的字符集

 在windows平台下,就是注册表里面相应OracleHome的NLS_LANG。

还可以在dos窗口里面自己设置,比如:

  Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

  这样就只影响这个窗口里面的环境变量。

 在unix平台下,就是环境变量NLS_LANG。

  $echo $NLS_LANG

  AMERICAN_AMERICA.ZHS16GBK

 如果检查发现server端与client端字符集不一致,请统一修改为同server端相同的字符集。

三、修改oracle的字符集

  上文说过,oracle的字符集有互相的包容关系。如us7ascii就是zhs16gbk的子集,从us7ascii到zhs16gbk不会有数据解释上的问题,不会有数据丢失。在所有的字符集中utf8应该是最大,因为它基于unicode,双字节保存字符(也因此在存储空间上占用更多)。

一旦数据库创建后,数据库的字符集理论上讲是不能改变的。因此,在设计和安装之初考虑使用哪一种字符集十分重要。根据Oracle的官方说明,字符集的转换是从子集到超集受支持,反之不行。如果两种字符集之间根本没有子集和超集的关系,那么字符集的转换是不受oracle支持的。对数据库server而言,错误的修改字符集将会导致很多不可测的后果,可能会严重影响数据库的正常运行,所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说,除非万不得已,我们不建议修改oracle数据库server端的字符集。

特别说明,我们最常用的两种字符集ZHS16GBK和ZHS16CGB231280之间不存在子集和超集关系,因此理论上讲这两种字符集之间的相互转换不受支持。

  1、修改server端字符集(不建议使用)

  在oracle 8之前,可以用直接修改数据字典表props$来改变数据库的字符集。但oracle8之后,至少有三张系统表记录了数据库字符集的信息,只改props$表并不完全,可能引起严重的后果。正确的修改方法如下:

  $sqlplus /nolog

  SQL>conn / as sysdba;

  若此时数据库服务器已启动,则先执行SHUTDOWN IMMEDIATE命令关闭数据库服务器,然后执行以下命令:

  SQL>STARTUP MOUNT;

  SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;

  SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

  SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;

  SQL>ALTER DATABASE OPEN;

  SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;

  SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;

  SQL>SHUTDOWN IMMEDIATE;

  SQL>STARTUP

  2、修改dmp文件字符集

因为dmp文件的第2、3字节记录了字符集信息,所以直接修改dmp文件的第2、3字节的内容就可以过oracle的检查。这样做理论上也仅是从子集到超集可以修改,但很多情况下在没有子集和超集关系的情况下也可以修改,我们常用的一些字符集,如US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK基本都可以改。因为改的只是dmp文件,所以影响不大。

  具体的修改方法比较多,最简单的就是直接用UltraEdit修改dmp文件的第2、3个字节。如果dmp文件很大,用ue无法打开,就需要用程序的方法了。

比如想将dmp文件的字符集改为ZHS16GBK,可以用SQL查出该种字符集对应的16进制代码:

    SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;

   0354

   然后将dmp文件的2、3字节修改为0354即可。

PS

  

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7830804/viewspace-863116/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-09-10

  • 博文量
    99
  • 访问量
    497196