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$.