26 April 2011

How to Change the 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:

  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;

No comments: