更改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是數(shù)據(jù)庫(kù)字符集，NLS_NCHAR_CHARACTERSET是國(guó)家字符集
Oracle中有兩大類(lèi)字符型數(shù)據(jù)，VARCHAR2是按照數(shù)據(jù)庫(kù)字符集來(lái)存儲(chǔ)數(shù)據(jù)。
而NVARCHAR2是按照國(guó)家字符集存儲(chǔ)數(shù)據(jù)的。同樣，CHAR和NCHAR也一樣，一是數(shù)據(jù)庫(kù)字符符，一是國(guó)家字符集。

轉(zhuǎn)換字符集，數(shù)據(jù)庫(kù)應(yīng)該在RESTRICTED模式下
首先要確定修改后的字符集是不是修改前的超集，如果不是可能出現(xiàn)相同的代碼點(diǎn)對(duì)應(yīng)不同的字符，出現(xiàn)亂碼的問(wèn)題。

出現(xiàn)這個(gè)錯(cuò)誤是 Oracle 只支持從子集到超集的轉(zhuǎn)變
那有什么方法可進(jìn)行強(qiáng)制轉(zhuǎn)換呢？
該指令會(huì)跳過(guò)子集與超集的檢驗(yàn)，當(dāng)然強(qiáng)制轉(zhuǎn)換可能會(huì)造成數(shù)據(jù)的損壞，要謹(jǐn)慎使用!!
下面借助eygle的帖子執(zhí)行。

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'

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]