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;

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