环境描述：
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
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.
