Oracle 11g修改字符集

oracle 11g 默认的字符集为: AL32UTF8,而且安装过程无法选择，安装完成后需要将其改为: ZHS16GBK.下面介绍了详细的修改方法。 1.数据库全备(新装的数据库可省略此步) 2.查询当前字符集

sys用户登陆oralce

SQL>select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; PARAMETER VALUE ---------------------------------------- ---------------------------------------- NLS_CHARACTERSET AL32UTF8

3.关闭数据库

SQL>shutdown immediate Database closed.

Database dismounted.

ORACLE instance shut down.

4.启动数据库到mount状态

SQL>startup mount

ORACLE instance started.

Total System Global Area 2055206 bytes Fixed Size 1266608 bytes Variable Size 10068 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 2055206 bytes Fixed Size 1266608 bytes Variable Size 10068 bytes Database Buffers 100663296 bytes

Redo Buffers 2924544 bytes Database mounted.

12.将相关参数改回原来值（改回第6步中查询到的值）

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

13.打开数据库

SQL>alter database open; Database altered.

14.修改完毕