在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> shutdownimmediateDatabaseclosed.Databasedismounted.
ORACLE instance shut down.
4.启动数据库到mount状态
SQL>startup mount
ORACLE instance started.
Total System Global Area205520896bytes
Fixed Size1266608bytes
Variable Size100666448bytesDatabase Buffers 100663296bytes
Redo Buffers2924544bytesDatabase mounted.
5.限制session
SQL> altersystem 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 setZHS16GBK;alter database character setZHS16GBK*ERROR at line1:
ORA-12712: new character set must be a superset of old character set
出现错误提示,新字符集必须是老字符集的超集,也就原来字符集是新字符集的子集,可以再Oracle官方文档上查询字符集包含关系。下面使用Oracle内部命令internal_use,跳过超集检查,生产环境不建议使用此方法。
SQL> alter database character setinternal_use ZHS16GBK;Database altered.
9.查询当前字符集
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE---------------------------------------- ----------------------------------------
NLS_CHARACTERSET ZHS16GBK
10.关闭数据库
SQL> shutdownimmediateDatabaseclosed.Databasedismounted.
ORACLE instance shut down.
11.启动数据库到mount状态
SQL>startup mount
ORACLE instance started.
Total System Global Area205520896bytes
Fixed Size1266608bytes
Variable Size100666448bytesDatabase Buffers 100663296bytes
Redo Buffers2924544bytesDatabase mounted.
12.将相关参数改回原来值
SQL> alter system set job_queue_processes=10;
System altered.
13.打开数据库
SQL> alter database open;Database altered.