Query to check character set in oracle

In this post, we will check about Query to check character set in oracle, What is Database Character set

What is Database Character set?

Oracle Database uses character sets for the following:

• Data stored in SQL character data types (CHAR, VARCHAR2, CLOB, and LONG).
• Identifiers include table names, column names, and PL/SQL variables.
• Stored SQL and PL/SQL source code, including text literals embedded in this code.

We have two character set defined for Oracle Database

The NLS_CHARACTERSET is used for CHAR, VARCHAR2, LONG and CLOB columns
The NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2 and NCLOB columns

Other points to remember

• Storage for non-character data types, such as NUMBER or DATE, does not depend on a character set
• WE8ISO8859P1/WE8MSWIN1252 are single byte character set
• AL32UTF8 is a multibyte character set
• Unicode is the universal character set that supports most of the currently spoken languages of the world.
• UTF8 and AL32UTF8 are the Unicode Character set
• Storage space requirements for text in most languages that use characters outside of the ASCII repertoire are higher in AL32UTF8 compared to legacy character sets supporting the language

Database Character Set Supported in Oracle

Here is the database character set and the languages supported in those character set

Query to check character set in oracle

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

Example

sqlplus / as sysdba
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
parameter               value
-------------           -------
NLS_CHARACTERSET           AL32UTF8 

Query to check the national character set in oracle

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

Example

sqlplus / as sysdba 
select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'; 
parameter               value 
-------------           ------- 
NLS_NCHAR_CHARACTERSET           AL16UTF16 

• From 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values: UTF8 or AL16UTF16 which are Unicode character sets.
• The NLS_NCHAR_CHARACTERSET defaults to AL16UTF16 if nothing is specified, this is the recommended value.

I hope you like this post. Please do provide the feedback

Related Articles
Query to check languages installed in oracle apps
oracle dba scripts: Top oracle dba scripts for Oracle Database for monitoring purposes, how to see the waits events of the sessions and check the last analyzed
Script to check locks in oracle database: Script to check locks in oracle database, unlock oracle table, find session holding oracle table locks, How to check the lock on the table in oracle
How to select blob from table
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/about-character-set-selection-during-installation.html#GUID-70B091B3-C67A-4AFD-9F60-75046171592D