Oracle 数据库 字符集修改

环境描述：

Linux 7.0  + Oracle 12C

问题描述：

将数据库NLS_CHARACTERSET与NLS_CHARACTERSET修改：

NLS_CHARACTERSET（是数据库字符集）由 AL32UTF8 修改为 ZHS16GBK

NLS_NCHAR_CHARACTERSET(是国家字符集) 由AL16UTF16 修改为 UTF8

解决办法：

代码：

conn /as sysdba 
shutdown immediate; 
startup mount 
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0;  
ALTER DATABASE OPEN;
----NLS_CHARACTERSET是数据库字符集
ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; 
--NLS_NCHAR_CHARACTERSET是国家字符集
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;
select * from v$nls_parameters;  
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
shutdown immediate; 
startup

操作如下：

SQL> conn / as sysdba
Connected.
SQL> SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16


SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount 
ORACLE instance started.

Total System Global Area 4999610368 bytes
Fixed Size    5293944 bytes
Variable Size 1258293384 bytes
Database Buffers 3724541952 bytes
Redo Buffers   11481088 bytes

Database mounted.

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.

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 ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; 

Database altered.

SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;

Database altered.

SQL> ALTER SESSION SET SQL_TRACE=FALSE;

Session altered.

SQL> SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

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


NLS_NCHAR_CHARACTERSET
UTF8


SQL> shutdown immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4999610368 bytes
Fixed Size    5293944 bytes
Variable Size 1258293384 bytes
Database Buffers 3724541952 bytes
Redo Buffers   11481088 bytes
Database mounted.
Database opened.