> 作者：threedayman
>
> 來源：[恆生LIGHT雲社群](https://developer.hs.net/thread/1748)

## **背景**

專案預設需要使用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

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
```

透過檢視當前字符集已經修改為ZHS16GBK。

注：生產環境字符集不要隨意進行修改，以免造成不可挽回的損失。

***

想向技術大佬們多多取經？開發中遇到的問題何處探討？如何獲取金融科技海量資源？

[恆生LIGHT雲社群](https://developer.hs.net/)，由恆生電子搭建的金融科技專業社群平臺，分享實用技術乾貨、資源資料、金融科技行業趨勢，擁抱所有金融開發者。

掃描下方小程式二維碼，加入我們！

![](https://developer.hs.net/storage/attachments/2021/12/20/vzjJlveZdvxcz6rUjIBaPMbRK36iL8jCjtMU5h97.png)

「Oracle」資料庫字符集編碼修改

相關文章