*** I would like to update this post since this is one of the most frequently accessed pages on my blog. The commands mentioned in this post can only be used till Oracle 9i. For Oracle 10g,11g,12c,18c Refer below links. ***
*** Updated 21-March-2018 ***
1. The official supported method for character set migration from Oracle 10g onwards is using CSSCAN, CSALTER and Export/Import utilities.
Support Link : Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)
2. For Oracle 12c a new tool called DMU (Database Migration Assistant for Unicode was released . Going forward DMU will be the only tool officially spported for migration to Unicode.
Support Link : The Database Migration Assistant for Unicode (DMU) Tool (Doc ID 1272374.1)
The procedure below can and only be used and I quote till Oracle 9i and “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 along with CSSCAN and CSALTER.
Here we go…..
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:
1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
3. Complete the following statements:
STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; ALTER DATABASE CHARACTER SET new_character_set; SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL; STARTUP; SQL > select * from nls_database_parameters ORDER BY PARAMETER;