修改oracle字符集简书,CentOS7.6.1810下修改Oracle字符集

前几天在CentOS7.6.1810下安装Oracle忘记了修改字符集，结果导入数据后发现中文乱码(DMP是从Windows上的Oracle导出的，字符集为ZHS16GBK)，于是在网上搜了一下，整理并亲身尝试过程如下：

1、启动sqlplus

sqlplus /nolog

2、以sysdba的角色进行连接

SQL> conn /as sysdba

3、查看当前字符集

select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.WE8MSWIN1252

4、开启restricted模式

SQL> alter system enable restricted session;

System altered.

5、修改字符集

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

意思就是说新设置的字符集要是原来的字符集的超集才行……，继续……

6、跳过超集检查

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.ZHS16GBK

我去……居然成功了……，其实……我第一次写的时候是还需要几步的，为了记录一下我恢复了虚拟机的快照，结果他……成功了……成功了……，我……又恢复了下……我要故障重现……，我……成功了

7、当有会话处于活动状态时

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

ALTER DATABASE character set INTERNAL_USE ZHS16GBK

*

ERROR at line 1:

ORA-12721: operation cannot execute when other sessions are active

8、重启下数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size 2213736 bytes

Variable Size 956303512 bytes

Database Buffers 620756992 bytes

Redo Buffers 7434240 bytes

Database mounted.

9、开启restricted模式，并修改字符集

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set INTERNAL_USE ZHS16GBK;

Database altered.

SQL> alter system disable restricted session;

System altered.

10、再次查看字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.ZHS16GBK

修改成功……