[转]更改Oracle字符集：把字符集ZHS16GBK换成UTF8

SQL> select name,value$ from props$ where name like '%NLS%';

NAME                           VALUE$

------------------------------ ------------------------------

NLS_LANGUAGE                   AMERICAN

NLS_TERRITORY                  AMERICA

NLS_CURRENCY                   $

NLS_ISO_CURRENCY               AMERICA

NLS_NUMERIC_CHARACTERS         .,

NLS_CHARACTERSET               ZHS16GBK

NLS_CALENDAR                   GREGORIAN

NLS_DATE_FORMAT                DD-MON-RR

NLS_DATE_LANGUAGE              AMERICAN

NLS_SORT                       BINARY

NLS_TIME_FORMAT                HH.MI.SSXFF AM

NAME                           VALUE$

------------------------------ ------------------------------

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_COMP                       BINARY

NLS_LENGTH_SEMANTICS           BYTE

NLS_NCHAR_CONV_EXCP            FALSE

NLS_NCHAR_CHARACTERSET         AL16UTF16

NLS_RDBMS_VERSION              10.2.0.1.0

20 rows selected.

NLS_CHARACTERSET是数据库字符集，NLS_NCHAR_CHARACTERSET是国家字符集

ORACLE中有两大类字符型数据，VARCHAR2是按照数据库字符集来存储数据。

而NVARCHAR2是按照国家字符集存储数据的。同样，CHAR和NCHAR也一样，一是数据库字符符，一是国家字符集。

转换字符集，数据库应该在RESTRICTED模式下

首先要确定修改后的字符集是不是修改前的超集，如果不是可能出现相同的代码点对应不同的字符，出现乱码的问题。

出现这个错误是 oracle 只支持从子集到超集的转变

那有什么方法可进行强制转换呢？

该指令会跳过子集与超集的检验，当然强制转换可能会造成数据的损坏，要谨慎使用!!

下面借助eygle的帖子执行。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1845493760 bytes

Fixed Size                  2021568 bytes

Variable Size             452986688 bytes

Database Buffers         1375731712 bytes

Redo Buffers               14753792 bytes

Database mounted.

SQL> alter session set sql_trace=true;

Session altered.

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 INTERNAL_USE UTF8;

Database altered.

SQL>update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET'

SQL> SHUTDOWN IMMEDIATE; 
SQL> STARTUP; 

 

转自：http://space.itpub.net/12778571/viewspace-624461