「Oracle」数据库字符集编码修改

原创

恒生LIGHT云社区 2022-03-18 09:38:10 ©著作权

文章标签 oracle superset 微信小程序 bash sqlplus 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者恒生LIGHT云社区的原创作品，请联系作者获取转载授权，否则将追究法律责任

作者：threedayman

背景

项目默认需要使用ZHS16GBK，当使用UTF8进行编码时，会出现插入数据超过字段长度限制问题。

原因：gbk编码中文字符占用两个字节，utf8编码中文字符时占用三个字节。

为了使初始化脚本能够正常运行，需要将数据库UTF8的编码改成ZHS16GBK编码**。**

修改步骤

通过以下命令连接到数据库

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 9 19:17:39 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

查看当前使用的字符编码

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

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

关闭数据库

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

启动

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.0267E+10 bytes
Fixed Size                 12684864 bytes
Variable Size            3355443200 bytes
Database Buffers         1.6844E+10 bytes
Redo Buffers               54423552 bytes
Database mounted.

修改

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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0267E+10 bytes
Fixed Size                 12684864 bytes
Variable Size            3355443200 bytes
Database Buffers         1.6844E+10 bytes
Redo Buffers               54423552 bytes
Database mounted.
Database opened.
SQL> select userenv('language') from dual;

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