Change Oracle Database Character Set : NLS_CHARACTERSET

*** 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;

 

Category: Database

Tags:

8 comments

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

    for example you cannot change UTF8 to US7ASCII

      1. I’ve tried this:
        ALTER DATABASE character set INTERNAL_USE new_char_set;
        It seems working, not sure if there’s any side effect.

  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

  4. if i wants to set NLS_CHARACTERSET on session level is it possible???????

    Please reply asap

    thanx in advance.

Leave a Reply

Article by: Shadab Mohammad