How to find and change the Character set of the Oracle database

The database character set in oracle determines the set of characters can be stored in the database. It is also used to determine 
the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

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

I am using Oracle 10g Database.Now the Character set as WE8MSWIN1252. 

I want to change my Character Set to UTF8. It is possible.

During the creation of your Oracle instance you do not have the option to select UTF8 as a character set. The default character set when creating Oracle databases is WE8MSWIN1252. If you perform an Advanced Installation of Oracle you can select a character set however the only Unicode option is AL32UTF8 which is a 4 byte version of UTF8 which is also not supported.

In order to check what character set your Oracle installation is using you can run the following query:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

In the screenshot below it can be seen that the character set in use is WE8MSWIN1252.

To resolve this issue you need to alter the character set that the database uses. To do this you need to run a series of queries against the database one at a time.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8; 

SHUTDOWN IMMEDIATE;

STARTUP;

If you then run the query to check the character set again you will see that it has been changed to UTF8.