Oracle数据库修改字符集编码

mahan

字符集编码格式一般都是ZHS16GBK，偶尔会遇到要改成AL32UTF8

复制代码
查看当前使用的字符集编码
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK   

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

以挂载方式打开数据库
SQL> startup mount
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size            2257272 bytes
Variable Size          507514504 bytes
Database Buffers      260046848 bytes
Redo Buffers            6828032 bytes
Database mounted.

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

提示我们的字符集：新字符集必须为旧字符集的超集，跳过超集检查做更改：
SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
Database altered.

重新启动数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size            2257272 bytes
Variable Size          507514504 bytes
Database Buffers      260046848 bytes
Redo Buffers            6828032 bytes
Database mounted.
Database opened.

查看更改后的字符集编码
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL>