修改資料庫字符集(轉）

在Redhat上安裝Oracle10g沒有設定字符集，採用的是作業系統預設字符集：US7ASCII，將字符集修改為：ZHS16GBK。由於過程不可逆，首先需要備份資料庫。
1.資料庫全備

2.查詢當前字符集
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET                         US7ASCII

3.關閉資料庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4.啟動資料庫到mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area  205520896 bytes
Fixed Size                  1266608 bytes
Variable Size             100666448 bytes
Database Buffers          100663296 bytes
Redo Buffers                2924544 bytes
Database mounted.

5.限制session
SQL> alter system enable restricted session;
System altered.

6.查詢相關引數並修改
SQL> show parameter job_queue_processes;  
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10

SQL> show parameter aq_tm_processes; 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0

SQL> alter system set job_queue_processes=0;
System altered.

7.開啟資料庫
SQL> alter database open;
Database altered.

8.修改字符集
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

出現錯誤提示，新字符集必須是老字符集的超集，也就原來字符集是新字符集的子集，可以再Oracle官方文件上查詢字符集包含關係。下面使用Oracle內部命令internal_use，跳過超集檢查，生產環境不建議使用此方法。

SQL> alter database character set internal_use ZHS16GBK;          
Database altered.

9.查詢當前字符集
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET                         ZHS16GBK

10.關閉資料庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

11.啟動資料庫到mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area  205520896 bytes
Fixed Size                  1266608 bytes
Variable Size             100666448 bytes
Database Buffers          100663296 bytes
Redo Buffers                2924544 bytes
Database mounted.

12.將相關引數改回原來值
SQL> alter system set job_queue_processes=10;
System altered.

13.SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

14.開啟資料庫
SQL> alter database open;
Database altered.