首页 > 代码库 > Oracle 12c中文乱码,修改字符集的方法

Oracle 12c中文乱码,修改字符集的方法

在windows 7 64位上安装Oracle 12c没有设定字符集,采用的是操作系统默认字符集:WE8MSWIN1252,将字符集修改为:ZHS16GBK。由于过程不可逆,首先需要备份数据库。
1.数据库全备

2.查询当前字符集
SQL> select * from nls_database_parameters where parameter=NLS_CHARACTERSET;PARAMETER                                VALUE---------------------------------------- ----------------------------------------NLS_CHARACTERSET                         WE8ISO8859P1

 3.关闭数据库

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

 4.启动数据库到mount状态

SQL> startup mountORACLE instance started.Total System Global Area  205520896 bytesFixed Size                  1266608 bytesVariable Size             100666448 bytesDatabase Buffers          100663296 bytesRedo Buffers                2924544 bytesDatabase 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     10SQL> show parameter aq_tm_processes; NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------aq_tm_processes                      integer     0SQL> 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.
11.启动数据库到mount状态
SQL> startup mountORACLE instance started.Total System Global Area  205520896 bytesFixed Size                  1266608 bytesVariable Size             100666448 bytesDatabase Buffers          100663296 bytesRedo Buffers                2924544 bytesDatabase mounted.
12.将相关参数改回原来值
SQL> alter system set job_queue_processes=10;System altered.
13.打开数据库
SQL> alter database open;Database altered.

 

Oracle 12c中文乱码,修改字符集的方法