Database Character Set for a Multitenant Container Database

Starting with Oracle Database 12c Release 2 (12.2), pluggable databases (PDBs) in a multitenant container database (CDB) can have different database character sets and different national character sets. 

The databases or PDB candidates that can be plugged into a CDB can be traditional independent databases or existing PDBs unplugged from other CDBs or newly created PDBs in the CDB.

The character set of the CDB root is considered as the character set of the whole CDB.

The following scenarios may occur depending upon the database character set of the PDB candidate that needs to be plugged into a CDB:

1. If the PDB candidate is an application PDB to be plugged into an application root:

1. 如果plugged pdb与application root相同字符集则成功

If the database character set of the PDB candidate is the same as the database character set of the application root, the plug-in operation succeeds.

1. 如果plugged pdb字符集是application root的binary subset, 则在pdb插入后第一次打开时自动转为application root字符集

If the database character set of the PDB candidate is a binary subset of the database character set of the application root and both are single-byte or both are multibyte, then the database character set of the PDB candidate is automatically changed to the database character set of the application root when the PDB candidate is opened for the first time and the plug-in operation succeeds.

1. 如果不是以上情况，虽然可以插入，但plugged pdb只能以restricted mode打开不能用于生产，你需要手动迁移到新pdb

If the database character set of the PDB candidate is not plug compatible with the database character set of the application root (when none of the above two scenarios apply), then the plug-in operation succeeds. But in this case the newly plugged-in PDB can be opened only in the restricted mode for performing administrative tasks and cannot be used for production. Unless you migrate the database character set of the new PDB to the database character set of the application root, the new PDB is unusable.

1. If the PDB candidate is to be plugged directly into the CDB root:

1. 如果相同则成功

If the database character set of the PDB candidate is the same as the database character set of the CDB, then the plug-in operation succeeds (as far as the database character set is concerned).

1. 如果cdb$root字符集为AL32UTF8，则pdb为任意字符集均成功

If the database character set of the CDB is AL32UTF8, then the plug-in operation succeeds regardless of the database character set of the PDB candidate.

1. 如果plugged pdb是cdb$root字符集的binary subset, 则在pdb插入后第一次打开时自动转为cdb$root字符集

If the database character set of the PDB candidate is plug compatible with the database character set of the CDB, that is, the database character set of the PDB candidate is a binary subset of the database character set of the CDB and both are single-byte or both are multibyte, then the database character set of the PDB candidate is automatically changed to the database character set of the CDB when the PDB candidate is opened for the first time and the plug-in operation succeeds.

1. 如果不是以上情况，虽然可以插入，但plugged pdb只能以restricted mode打开不能用于生产，你需要手动迁移到新pdb

If the database character set of the PDB candidate is not plug compatible with the database character set of the CDB, that is, when none of the last three scenarios mentioned above apply, then the plug-in operation succeeds. But, in this case the newly plugged-in PDB can be opened only in the restricted mode for performing administrative tasks and cannot be used for production. Unless you migrate the database character set of the new PDB to the database character set of the CDB, the new PDB is unusable.

The following scenarios may occur depending upon the national character set of the PDB candidate that needs to be plugged into a CDB:

1. If the PDB candidate is an application PDB to be plugged into an application root:

1. 如果nation character set相同，则成功

If the national character set of the PDB candidate is the same as the national character set of the application root, then the plug-in operation succeeds (as far as the national character set is concerned).

1. 如果不同，虽然可以插入，但plugged pdb只能以restricted mode打开不能用于生产，你需要手动迁移到新pdb

If the national character set of the PDB candidate is not the same as the national character set of the application root, then the plug-in operation succeeds. But, in this case the newly plugged-in PDB can be opened only in the restricted mode for performing administrative tasks and cannot be used for production. Unless you migrate the national character set of the new PDB to the national character set of the application root, the new PDB is unusable.

1. 如果插入的是cdb$root，则不用考虑nation character set

If the PDB candidate is to be plugged directly into the CDB root, then the plug-in operation succeeds (as far as the national character set is concerned).

Note:

When a PDB character set is different from the CDB character set, there may be data truncation, if the column widths of CDB views and V$ views are not able to accommodate the PDB data that has expanded in length during the character set conversion.

As UTF8 and AL32UTF8 have different maximum character widths (three versus four bytes per character), the automatic change of UTF8 to AL32UTF8 during plug-in operation will change implicit maximum byte lengths of columns with character length semantics. This change may fail, if there are functional indexes, virtual columns, bitmap join indexes, domain indexes, partitioning keys, sub-partitioning keys, or cluster keys defined on those columns. The plug-in operation may also fail, if a character length semantics column is part of an index key, and the index key exceeds the size limit (around 70% of the index block size) after the character set change. You must make sure that all the offending objects are removed from a database before it is plugged into a CDB. You can recreate those offending objects in the database after the database is plugged into a CDB.

Because of these restrictions, Oracle recommends the following when selecting character sets for CDBs:

1. For all new multitenant deployments, use AL32UTF8 as the database character set and AL16UTF16 as the national character set for a CDB.
2. Migrate your existing databases to AL32UTF8 database character set before consolidation合并and then consolidate the databases into one or more AL32UTF8 CDBs, depending on your needs. You can use the Oracle Database Migration Assistant for Unicode software to migrate a non-CDB to AL32UTF8 database character set.