How To Check Oracle Database Character Set

| 26-03-2021
[RAND-1-100] Comments

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

  1. How To Know Oracle Database Character Set
  2. How To Check Oracle Database Character Set Up
  3. How To Check Nls Character Set In Oracle Database
  4. Check Oracle Database Character Set

Home » Articles » 12c » Here

In the previous release the character set for the root container and all pluggable databases associated with it had to be the same. This could limit the movement of PDBs and make consolidation difficult where a non-standard character set was required.

  • However, if the variable's maximum length is now expressed in characters, SUBSTR needs to be used. However, if SUBSTR is used to extract a functional part of a string (e.g. During parsing), possibly based on result from INSTR, then you should use SUBSTR and INSTR independently of the database character set - characters matter here, not bytes.
  • Conclusion: in this post we saw that how to check the Oracle Database Characterset using the query and setting the same while installation to avoid any further issues. To find out the database characterset you can use databaseproperties table which will show you all the nls database parameter values.

In Oracle Database 12c Release 2 (12.2) a PDB can use a different character set to the CDB, provided the character set of the CDB is AL32UTF8, which is now the default character set when using the Database Configuration Assistant (DBCA).

The tool to change a PDB’s character set is the Data Migration Assistant for Unicode (DMU). DMU is installed in all Oracle releases since Oracle 11.2.0.4 by default. Just check the $ORACLEHOME/dmu subdirectory and start the DMU with sh dmu.sh. And find more information about the DMU here: DMU – Tips and Tricks. Either use NVARCHAR2, resp. NCLOBdata type columns (typically NLSNCHARCHARACTERSETsupports Unicode) or migrate character set of you database properly, see Character Set Migration– Wernfried DomscheitMar 31 '17 at 6:33 add a comment 2 Answers 2.

If you are using 12.2.0.1, make sure you've applied a patch for bug 25054064 (marked as fixed in 18.1) before doing anything serious with this feature. Thanks to Patrick Jolliffe for pointing this out.

Related articles.

How to check oracle database character set up

How To Know Oracle Database Character Set

Check the Destination CDB Character Set

Connect to the destination root container and run the following query to display the default character set of database.

We can see the default character set of the root container is AL32UTF8, which means it can hold PDBs with different character sets.

Create a Source CDB and PDB

First we must create a CDB with the WE8ISO8859P1 character set so we have a suitable source CDB and PDB. The following command creates a CDB called cdb3 with a PDB called pdb5

12c

We make the source CDB use Oracle Managed Files (OMF) and switch it to archivelog mode.

How To Check Oracle Database Character Set

Hot Clone the Source PDB

To prove we can house a database of a different character set in our destination CDB, we will be doing a hot clone. The setup required for this is described in the following article.

Once you've completed the setup, you can perform a regular hot clone. Connect to the destination CDB.

How To Check Oracle Database Character Set Up

Clone the source PDB (pdb5) to create the destination PDB (pdb5new).

Open the PDB for the first time.

How To Check Nls Character Set In Oracle Database

If you have any problems, check the PDB_PLUG_IN_VIOLATIONS view. When I first wrote this article against an instance on Oracle Cloud I did not see any violations. On the on-prem 12.2.0.1 I see the following Unicode violation, but this doesn't stop the new PDB from working.

Check the Destination PDB

Compare the character set of the CDB and the new pluggable database.

We can see we have a pluggable database with a different character set to that of the root container.

Miscellaneous

  • The root container must use to the AL32UTF8 character set if you need it to hold PDBs with differing character sets.
  • The character set and national character set of an application container and all its application PDBs must match.
  • New PDBs, cloned from the seed database, always match the CDB character set. There is no way to create a new PDB with a different character set directly. You can use Database Migration Assistant for Unicode (DMU) to convert the character set of a PDB.
  • As seen in this article, cloning can be used to create a PDB with a different character set, as can unplug/plugin.
  • LogMiner supports PDBs with different character sets compared to their CDB.
  • Data Guard support PDBs with different character sets compared to their CDB for rolling upgrades.

For more information see:

Check Oracle Database Character Set

Hope this helps. Regards Tim...