ITPub博客

首页 > 数据库 > Oracle > 手工完成oracle 字符集的转换

手工完成oracle 字符集的转换

Oracle 作者:gskaren 时间:2010-10-12 16:22:41 0 删除 编辑
产生这个问题的原因:

很多人安装linux时喜欢选择西文系统,这是习惯问题,有些下载版本的linux 缺省安装也是西文系统,通常西文系统并不影响部署在上面的中文应用的运行.

在西文linux下安装oracle的时候,oracle installer会自动根据当前系统选择编码,而你安装的时候如果不定制,那装出来的就是西文编码,NLS_CHARACTERSET=WE8ISO8859P,而我们需要的是NLS_CHARACTERSET=ZHS16GBK,ZHS16GBK是中文简体

这样的后果,就是装出来的oracle不支持中文库表和中文记录数据,全是乱码


查看oracle 当前字符集
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';     
PARAMETER                                                    VALUE
------------------------------------------------------------ -----------------------
NLS_CHARACTERSET                                             WE8ISO8859P1
常用的一些字符集,如 US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK, AL32UTF8


修改linux当前编码(option)
虽然这个步骤不是必须的,但还是建议做:
#cat /etc/sysconf/i18n
如果你看到如下,则说明你的linux是西文系统;
LANG="en_US.UTF-8"
改成如下,重启系统,你的linux就是中文系统了
LANG="zh_CN"
如果你看到如下几种配置,都说明你的系统是中文系统,不需要更改
LANG="zh_CN"
LANG="zh_CN.UTF-8"
LANG="zh_CN.GBK"
LANG="zh_CN.GB2312"
LANG="zh_CN.GB18030"


为oracle用户加一个环境变量
[oracle@oracle bin]$ cd /home/oracle
[oracle@oracle ~]$ cat .bash_profile
# .bash_profile

umask 022

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1/
export ORACLE_HOME_LISTNER=$ORACLE_HOME
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"         加此句
export PATH=$PATH:$ORACLE_HOME/bin



以mount 模式启动oracle 系统--------这种启动模式通常用于维护,备份,恢复
[oracle@oracle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 4 13:35:54 2010

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

SQL> conn /as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount   
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size             117442152 bytes
Database Buffers          159383552 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> 


开始改字符集
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 DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

提示我们的字符集:新字符集必须为旧字符集的超集
我们可以跳过超集的检查做更改:
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.

看结果
SQL> set linesize 300

SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE                                                     SIMPLIFIED CHINESE
NLS_TERRITORY                                                    CHINA
NLS_CURRENCY                                                    
NLS_ISO_CURRENCY                                                 CHINA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                        SIMPLIFIED CHINESE
NLS_CHARACTERSET                                         ZHS16GBK
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                               
NLS_NCHAR_CHARACTERSET                                           AL16UTF16
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE


系统恢复,重新启动
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size             117442152 bytes
Database Buffers          159383552 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> 













<!-- 正文结束 -->

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

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-15