Oracle 10g修改資料庫字元集

查看當前字元集

引用
SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1
NLS_NCHAR_CHARACTERSET         AL16UTF16

引用
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

嘗試直接修改字元集

引用
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 10g中建議用CSALTER修改！
）

引用
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

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 zhs16gbk;

Database altered.

修改時alert日誌顯示：

Completed:  ALTER DATABASE OPEN
Tue May 18 10:56:43 2010
ALTER DATABASE CHARACTER SET INTERNAL_USE zhs16gbk
Tue May 18 10:56:47 2010
Updating character set in controlfile to ZHS16GBK
Synchronizing connection with database character set information
Refreshing type attributes with new character set information
Completed: ALTER DATABASE CHARACTER SET INTERNAL_USE zhs16gbk

查看修改後結果：

SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK

NLS_NCHAR_CHARACTERSET
AL16UTF16

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK