ORACLE19c 修改pdb字符集编码

一、 db默认字符集AL32UTF8
Specify the database character set when you create the database. Starting from Oracle Database 12c Release 2, if you use Oracle Universal Installer (OUI) or Oracle Database Configuration Assistant (DBCA) to create a database, then the default database character set used is the Unicode character set AL32UTF8.

12.2之前版本的数据库中，数据库的默认字符集都是根据操作系统的字符集来定的。在12.2中，在安装数据库的时候，数据库默认的字符集为AL32UTF8。

二、  PDB支持不同字符集

Per-PDB Character Set的有几个前提条件：
1） CDB must be AL32UTF8
2） Application Container requires single character set
3） National character set also supported per PDB
4） Truncation of data can occur in cross-container queries if data conversion to UNICODE causes expansion

从12.2起引入新特性同一CDB中每个PDB可以使用不同的字符集，前提CDB是AL32UTF8。 如CDB是AL32UTF8，其中的PDB1为ZHS16GBK , 从其它CDB plug-in一个其他字符集的PDB也是允许的，在plug-in PDB过程中并不会转换字符集。

对于最新版本 12.2.0.1 ，在新创建 PDB 时并没有办法指定 PDB 的字符集，因此只能和 root 容器字符集相同。目前没有提供创建PDB指定字符集的选项。

可以通过以下方式实现:

a. 而不同 PDB 可以使用 hot clone, 
b. Relocate PDB online 方式直接 plug-in 
c. 可使用内部转换字符集( internal_use) 进行转换
d. 使用12 DMU 工具

[oracle@rac1 ~]$ sqlplus / as sysdba



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


SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 TSPDB   READ WRITE NO
SQL> 
SQL> 
SQL> 
SQL> select userenv('language') from dual;


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


SQL> 
SQL> 
SQL> alter session set container =tspdb;


Session altered.


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


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


SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@rac1 ~]$ sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 31 16:25:42 2021
Version 19.3.0.0.0

SQL> 
SQL> 
SQL> show pdbs                                           


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 TSPDB   READ WRITE NO
SQL> 

SQL> alter pluggable database TSPDB close instances=all;


Pluggable database altered.


SQL> 
SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 TSPDB   MOUNTED
SQL> 
SQL> 
SQL> alter pluggable database TSPDB open read write restricted;


Pluggable database altered.


SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 TSPDB   READ WRITE YES
SQL> 
SQL> 
SQL> alter session set container=TSPDB;


Session altered.


SQL> 
SQL> alter database character set internal_use ZHS16GBK;     


Database altered.


SQL> 
SQL> alter pluggable database TSPDB close;


Pluggable database altered.


SQL> 
SQL> 
SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 TSPDB   MOUNTED
SQL> 
SQL> 
SQL> alter pluggable database TSPDB open instances=all;


Pluggable database altered.


SQL> 
SQL> 
SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 TSPDB   READ WRITE NO
SQL> 
SQL> 
SQL> select userenv('language') from dual;


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


SQL> 
SQL> 
SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> show pdbs


    CON_ID CON_NAME   OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED   READ ONLY  NO
3 TSPDB   READ WRITE NO
SQL> select userenv('language') from dual;


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