ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 字符集的修改问题

oracle 字符集的修改问题

原创 Linux操作系统 作者:aikangyan 时间:2008-01-22 14:09:56 0 删除 编辑

oracle9204更改字符集步骤方法:
由于我们经常做不同数据库的导出导入时遇到的服务器的字符集不同,导致我们原有的无法正常导入工作,
这时需要我们做数据库字符集的更改工作,
字符集是在安装数据库时选定的,一旦数据库创建之后,数据库的字符集是不能改变的,如果要改变数据库的字符集,只有重新创建数据库。
但通常也可以在特定的条件下通过一些其他途径来完成字符集的转换(这里强烈建议创建数据库时就要考虑好字符集)
单字节=>单字节
多字节=>多字节
且是子集到超集时,可以用alter database CHARACTER set 新的字符集;
通常对于单字节向多字节转换和非子集到超集转换时采用exp/imp+internal_use的方法
这里只介绍AL32UTF8>UTF8的步骤方法:

检查当前的字符集为:
SQL> set wrap off
SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 AL32UTF8
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

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

19 rows selected.

SQL>

将数据库启动到RESTRICTED模式下做字符集更改:
SQL> conn /as sysdba 
注意这里,必须这样登陆,后面的shutdown and startup operation 才能成功
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
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 UTF8;
ALTER DATABASE CHARACTER SET UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:

SQL> ALTER DATABASE character set INTERNAL_USE UTF8;

Database altered.

SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;

Database altered.

 

SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 UTF8
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           UTF8
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE

19 rows selected.


重启检查是否更改完成:

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

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE                                                     AMERICAN
NLS_TERRITORY                                                    AMERICA
NLS_CURRENCY                                                     $
NLS_ISO_CURRENCY                                                 AMERICA
NLS_NUMERIC_CHARACTERS                                           .,
NLS_CALENDAR                                                     GREGORIAN
NLS_DATE_FORMAT                                                  DD-MON-RR
NLS_DATE_LANGUAGE                                                AMERICAN
NLS_CHARACTERSET                                                 UTF8
NLS_SORT                                                         BINARY
NLS_TIME_FORMAT                                                  HH.MI.SSXFF AM

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT                                             DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT                                               HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT                                          DD-MON-RR HH.MI
NLS_DUAL_CURRENCY                                                $
NLS_NCHAR_CHARACTERSET                                           UTF8
NLS_COMP                                                         BINARY
NLS_LENGTH_SEMANTICS                                             BYTE
NLS_NCHAR_CONV_EXCP                                              FALSE

19 rows selected.

SQL> 

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

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

注册时间:2007-12-12

  • 博文量
    81
  • 访问量
    42706