oracle12c+修改字符集,Oracle12c更改数据库字符集为ZHS16GBK

查询数据库字符集，将字符集改为ZHS16GBK

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_CHARACTERSET

WE8MSWIN1252

SQL> show pdbs

CON_ID CON_NAME  OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED  READ ONLY  NO

3 PDB_ORCL  READ WRITE NO

SQL> show pdbs

CON_ID CON_NAME  OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED  READ ONLY  NO

3 PDB_ORCL  READ WRITE NO

关闭数据库

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

启动数据库到mount模式

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.6267E+11 bytes

Fixed Size    7653480 bytes

Variable Size 2.4159E+10 bytes

Database Buffers 1.3798E+11 bytes

Redo Buffers  529215488 bytes

Database mounted.

限制session并修改相关参数(记住参数值，最后更改完字符集需要将这些参数改成原值)

SQL> alter system enable restricted session;

System altered.

SQL>  show parameter job_queue_processes;

NAME     TYPE

------------------------------------ ----------------------

VALUE

------------------------------

job_queue_processes     integer

1000

SQL> show parameter aq_tm_processes;

NAME     TYPE

------------------------------------ ----------------------

VALUE

------------------------------

aq_tm_processes     integer

1

SQL> alter system set job_queue_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

出现错误提示，新字符集必须是老字符集的超集，也就原来字符集是新字符集的子集。

使用Oracle内部命令internal_use跳过使用超集检查。

SQL> alter database character set internal_use ZHS16GBK;

alter database character set internal_use ZHS16GBK

*

ERROR at line 1:

ORA-12720: operation requires database is in EXCLUSIVE mode

报错信息要求数据库在EXCLUSIVE模式下，修改集群数据库参数为false(单库不需要调整该参数

，该参数在修改完字符集之后需要改回原值)并重启数据库

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

注意：启动数据库需要启动到restrict模式下，不然在修改字符集的时候可能汇报如下错误：

ORA-12719: operation requires database is in RESTRICTED mode

SQL> startup restrict

ORACLE instance started.

Total System Global Area 1.6267E+11 bytes

Fixed Size    7653480 bytes

Variable Size 2.4159E+10 bytes

Database Buffers 1.3798E+11 bytes

Redo Buffers  529215488 bytes

Database mounted.

Database opened.

SQL> alter database character set internal_use ZHS16GBK;

Database altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.6267E+11 bytes

Fixed Size    7653480 bytes

Variable Size 2.4159E+10 bytes

Database Buffers 1.3798E+11 bytes

Redo Buffers  529215488 bytes

Database mounted.

Database opened.

查询修改结果并将上述修改的参数调整为原来的值。

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';