** I would like to update this post since this being one of the most highly accessed page on my blog. The official supported method for character set migration from Oracle 10g onwards is using CSSCAN and CSALTER utility. From 12c onwards Oracle introduced a new tool called DMU (Database Migration Assistant for Unicode), DMU is compatible backwards till 220.127.116.11. You can refer the below links for more information **
The procedure below can and only be used and I quote “only if the new character set is a strict superset of the current character set.” And cannot be used to migrate single byte to multiple byte character set. For that you should be using Export/Import or Data Pump utility.
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;
SQL > select * from nls_database_parameters ORDER BY PARAMETER;