Home > Oracle, RDBMS > How to change the character set of the database.

How to change the character set of the database.

Caution! Changing the character set can sometimes cause data loss or data corruption.
I strongly encourage to make a full backup of the database, before attempting to migrate the data to a new character set.
It is also a good idea to read something about changing character set in the Oracle, here are some useful links:

Anyway, here is the procedure to change the character set of the Oracle database.

1. Check the current database character set.

select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';

2. Sometimes it could be not enough – here is the query to check ALL database character sets.

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$
where charsetform in (1,2)
and type# in (1, 9, 96, 112);

3. Log into the database and do a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

If for whatever reason, the database does not get shut down cleanly (via a shutdown immediate command), start it back up in restrict mode and shut it down again.

4. Mount the database.

STARTUP MOUNT;

5. Restrict logon to the database, disable job processes and queue processes.

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

6. Open the database.

ALTER DATABASE OPEN;

7. Change the character set (instead of &CHARSET use the proper character set, e.g. ‘EE8MSWIN1250’).

ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;

8. You can also change the national character set (instead of &NCHARSET use the proper character set, e.g. ‘AL16UTF16’)

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

9. Make a clean shutdown of the database.

SHUTDOWN IMMEDIATE;

10. Start it up.

STARTUP;

That’s should be all.

Advertisements
Categories: Oracle, RDBMS Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: