Oracle服務(wù)器數(shù)據(jù)庫(kù)中文亂碼之字符集修改

最近安裝個(gè)Oracle 10g數(shù)據(jù)庫(kù)，dbca建庫(kù)的時(shí)候沒(méi)選字符集或者選錯(cuò)的情況，導(dǎo)入數(shù)據(jù)后，中文全部是亂碼（搞笑的是，中文亂碼 居然全部是個(gè)“靠”字），查看網(wǎng)絡(luò)幾篇文章，也做了相應(yīng)修改（以前按方法修改可以解決的，今天特例�。�，解決辦法如下：
SQL> connect / as sysdba;              ---dba用戶連接數(shù)據(jù)庫(kù)
-
 

SQL> select name,value$ from props$ where name like '%NLS%';---語(yǔ)句查詢當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器字符集

NAME
------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$

NAME
------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET           ----找到查出來(lái)的結(jié)果中的該項(xiàng)參數(shù)
WE8ISO8859P1

下面是修改過(guò)程：

SQL> shutdown immediate;     ---停止數(shù)據(jù)庫(kù)
Database closed.
Database dismounted.
Oracle instance shut down.

SQL> startup mount           ---啟動(dòng)數(shù)據(jù)庫(kù)到mount狀態(tài)
Oracle instance started.

Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.

---下面是修改過(guò)程，按步驟修改
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> set linesize 120;
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;

# 使用INTERNAL_USE可以跳過(guò)超集的檢查,

（ALTER DATABASE character set INTERNAL_USE ）

Database altered.
SQL> shutdown immediate;      ---再停止數(shù)據(jù)庫(kù)
Database closed.
Database dismounted.
Oracle instance shut down.

---啟動(dòng)數(shù)據(jù)庫(kù)
SQL> STARTUP
Oracle instance started.

Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

----再次查看當(dāng)前字符集

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

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
?

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
ZHS16GBK  ---【已經(jīng)修改成為中文編碼了】

今天的情況特殊，修改完了，中文亂碼還是“靠”字，- -

后來(lái)試著修改系統(tǒng)字符集

在Oracle下 .bash_profile 文件里面加入

export NLS_LANG="Simplified Chinese_CHINA.ZHS16GBK"
再次登錄查看，還是不行

最后把NLS_LANG中后面的字符集ZHS16GBK修改成AL32UTF8，查看結(jié)果，搞掂完成，回家！

總結(jié)：要系統(tǒng)字符集和數(shù)據(jù)庫(kù)字符集相對(duì)應(yīng)才會(huì)解決中文亂碼問(wèn)題,dbca建庫(kù)時(shí)，也要注意字符集選擇問(wèn)題。

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