How to change the character set of the database.

How to change the character set of the database.

1. Check the current database character set.

1 2 3

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.

1 2 3 4 5 6 7

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.

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

Note --Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

4. Mount the database.

1	STARTUP MOUNT;

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

1 2 3

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

6. Open the database.

1	ALTER DATABASE OPEN;

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

1	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’)

1	ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

9. Make a clean shutdown of the database.

1	SHUTDOWN IMMEDIATE;

10. Start it up.

1

STARTUP;

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.