Problem solve Get help with specific problems with your technologies, process and projects.

Changing database character set, part 1

Is it possible to alter the character set of my production DB without re-creating the DB (making some changes in the NLS_LANG in the registry, will there be of any effect)? Can you guide me?
Your best bet is to read Metalink note 225912.1 which discusses this in detail. Just in case you don't have Metalink access, I've included the body of the note here:

This article gives a overview of methods to change the database character set. Use this note to get a basic understanding of the methods, then use the in-depth notes at the end of this note for specific guidance on your conversion.

The database character set
The character set of a database defines how characters are stored in the database. Therefore you are limited to storing just the characters defined in that character set. If you change character sets there is a possibility that characters that you currently use are not defined in the new character set and therefore you could 'corrupt' your data. You should always check this by using the Character Set Scanner (csscan) or by following [NOTE:225938.1] Database Character Set Healthcheck before making any changes to your character set.

Please do NOT skip this!

Changing the database character set
There are two basic ways of changing the character set and a third 'combined' way:

This is not always possible because this does not change the actual code points of the stored data. So this method can only be used if the data that is currently stored in the database is defined under the same code points in the new character set. This is documented in [NOTE:66320.1] Changing the Database Character Set or the Database National Character Set and can be used for these combinations:
[NOTE:119164.1] Changing Database Character Set - Valid Superset Definitions

2. Using Export/Import
This will always work, you simply export the current database, then create a new database with the new character set and import the data into that database. Of course the characters that you were storing will still have to be defined in the new character set for this to work!

See [NOTE:227332.1] NLS considerations in Import/Export - Frequently Asked Questions

3. Using a combination of ALTER DATABASE CHARACTER SET and export/import In some cases method 1 does not work because csscan tells you that some data needs to be converted to the new character set, and method 2 will simply take too much time. In those cases it is usualy possible to use a combination of the two methods:
a) Export the data from the tables that need to be converted
b) Truncate or drop those tables.
c) Run csscan again to confirm that all data is now ready to be moved to the new character set directly and if that is the case change the character set of the database using the ALTER DATABASE CHARACTERSET command (method 1).
d) Now that the character set has changed we can simply import the data exported in step (a). The import will convert that data so that it gets stored in the correct way for this character set.

Click to continue in part 2.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.