How to change database character set

This article gives a overview of methods to change the database character set .


Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;