How to change the character set of the database.

Caution! Changing the character set can sometimes cause data loss or data corruption.
I strongly encourage to make a full backup of the database, before attempting to migrate the data to a new character set.
It is also a good idea to read something about changing character set in the Oracle, here are some useful links:

• Oracle 9i
• Oracle 10g
• Oracle 11g

Anyway, here is the procedure to change the character set of the Oracle database.

1. Check the current database character set.

select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';

2. Sometimes it could be not enough – here is the query to check ALL database character sets.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$
where charsetform in (1,2)
and type# in (1, 9, 96, 112);

3. Log into the database and do a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

If for whatever reason, the database does not get shut down cleanly (via a shutdown immediate command), start it back up in restrict mode and shut it down again.

4. Mount the database.

STARTUP MOUNT;

5. Restrict logon to the database, disable job processes and queue processes.

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

6. Open the database.

ALTER DATABASE OPEN;

7. Change the character set (instead of &CHARSET use the proper character set, e.g. ‘EE8MSWIN1250’).

ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;

8. You can also change the national character set (instead of &NCHARSET use the proper character set, e.g. ‘AL16UTF16’)

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

9. Make a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

10. Start it up.

STARTUP;

That’s should be all.