如何修改Oracle資料庫字元集

環境:
OS:Red Hat Linux As 5
DB:Oracle 10.2.0.4
 
通常情況下,字元集是在安裝的時候選定好的,需要修改資料庫的字元集Oracle建議的做法是重建資料庫(EXP/EXPDP匯出後再匯入),雖然Oracle官方文檔也有說如何修改字元集,但這不是Orale推薦的方法.下面是Oracle官方文檔中提到的如何修改字元集的方法.
 
1.查看當前資料庫字元集
select * from nls_database_parameters
where parameter='NLS_CHARACTERSET';
-----------------------------------
PARAMETER         VALUE
NLS_CHARACTERSET WE8ISO8859P1
 
這裡的字元集WE8ISO8859P1是不支援中文的
 
2.將字元集修改為中文字元集ZHS16GBK
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
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> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
 
這樣將資料庫的字元集由原來的WE8ISO8859P1修改成了ZHS16GBK,對原來的字元集有中文的顯示的還是亂碼,但對新入庫的中文字元集就能正常顯示.
 
-- The End --