oracle10g更改字符集AL32UTF8为ZHS16GBK

oracle10g更改字符集AL32UTF8为ZHS16GBK .

Database character set (AL32UTF8) and Client character set (ZHS16GBK) are different.
Character set conversion may cause unexpected results.
Note: you can set the client character set through the NLS_LANG environment variable or the NLS_LANG registry key in
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraDb11g_home

SIMPLIFIED CHINESE_CHINA.ZHS16GBK 包含 AMERICAN_AMERICA.AL32UTF8.
这可是个麻烦事，不是改客户端字符集的问题。要改数据库的字符集：
SQL> conn /as sysdba
SQL> 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;
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;
--我们看到这个过程和之前ALTER DATABASE CHARACTER SET操作的内部过程是完全相同的，也就是说INTERNAL_USE提供的帮助就是使Oracle数据库绕过了子集与超集的校验.
SQL> select * from v$nls_parameters; 
SQL> shutdown immediate;
SQL> startup
SQL> select * from v$nls_parameters;
SQL> select userenv('language') from dual;
ok修改成功！

实验步骤：
[oracle@localhost tmp]$ sqlplus /  as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 2 16:33:05 2011

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

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

Total System Global Area  536870912 bytes
Fixed Size                  1220432 bytes
Variable Size             155189424 bytes
Database Buffers          377487360 bytes
Redo Buffers                2973696 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 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> select * from v$nls_parameters;

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CALENDAR
GREGORIAN

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN

NLS_CHARACTERSET
ZHS16GBK

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE

19 rows selected.

SQL>

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

Total System Global Area  536870912 bytes
Fixed Size                  1220432 bytes
Variable Size             155189424 bytes
Database Buffers          377487360 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

 

重新导入dmp文件
--源操作系统的环境变量
LANG=en_US.UTF-8

就需要在导入前设置环境变量
export LANG=GBK;
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

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

转载于:http://blog.itpub.net/24531354/viewspace-712741/