What is a database character set?

The NLS_CHARACTERSET of an Oracle database defines what characters can be stored in the database using the CHAR, VARCHAR2, LONG and CLOB datatypes. A Character set does not define languages, it defines a certain range of characters. Any language that uses the characters known by that characterset can then be stored.

If you change character sets there is a possibility that characters that you currently use are not defined in the new character set or that the current setup is not correctly used and therefore you could lose data when changing the NLS_CHARACTERSET. Always check this by using the Character Set Scanner (Csscan) before making any changes to your character set. Even when using Exp/imp or Expdp/Impdp.

Determining a Database Character Set

The database character set information is stored in the data dictionary tables named SYS.PROPS$.

You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name.

As ‘show parameter’ does not tell you your database char set, Here I’m listing some commands to find the database character set:

SQL> select * from nls_database_parameters
  2  where parameter="NLS_CHARACTERSET";

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
SQL> SELECT value$ FROM sys.props$ WHERE name="NLS_CHARACTERSET" ;

VALUE$
---------------------------------------------------------------------------

WE8MSWIN1252 
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.1.0.6.0

20 rows selected.

A Word of Caution

There are still “dba’s” out there who try to change the NLS_CHARACTERSET or NLS_NCHAR_CHARACTERSET by updating props$. This is NOT supported and WILL corrupt your database. This is one of the best ways’s to destroy a complete dataset.

The first step to take if this is done is a complete restore of the database. If no backup is available Oracle Support will TRY to help you out of this but Oracle will NOT warrant that the data can be recovered or recovered data is correct and you WILL be asked to do a FULL export and a complete rebuild of the database.

Please, do NOT update props$.