Change Oracle Database Character Set : NLS_CHARACTERSET

** 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 11.2.0.3. You can refer the below links for more information **

Character Set Migration using CSSCAN and CSALTER

Database Migration Assistant for Unicode (DMU) in Oracle Database 12c

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;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;

About these ads

10 comments on “Change Oracle Database Character Set : NLS_CHARACTERSET

  1. you can change the database character only to it’s super set character set.

    for example you cannot change UTF8 to US7ASCII

  2. Like I have mentioned in my blogs Disclaimer. Always try this in your testing environment before applying it on a production system. But FYI Necati I have actually used this method to change a characterset on a DR database without any worries. I wouldnt recommend anyone to do it o na production system. But personally it has worked for me.

  3. u know if I can change from AL32UTF8–WE8ISO8859P15 ? I’ve been trying to get the superset list and I haven’t find one that have WE8ISO8859P15 like a superset of AL32UTF8. Please let me know if I can change it without implications

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s