Tuesday, April 04, 2006

Changing WE8ISO8859P1 to UTF8 with ALTER DATABASE CHARACTERSET!

Today I did something "tricky" with the Oracle database... I went from one characterset to another although it was not a superset/subset of eachother.

The client requested to go from WE8ISO8859P1 to UTF8 characterset.
You would normally create a new database with the new characterset and do an export/import, but I looked for an other solution.
Why did I do that?
- I didn't have enough free space to create a new database
- I didn't want the people have to change all connection settings
- It needed to go as quick as possible and on 3 environments (dev, test, prod)

I found a note on Metalink (Note:260192.1) which described how you could do it. Basically I followed it, and the first tests after the change in characterset were good.
I will provide a zip-file with all the scripts I used and the steps I followed. A Metalink note is good, but it always refers to other notes... In the zip-file you see how I did it, the most important file is change_charset.sql. The zip-file can be downloaded here.

Good luck with the conversion.

6 comments:

Dimitri Gielis said...

I'm just testing the comment

Anonymous said...

Nice tip, thanks.

Anonymous said...

When I try to download the zip file with the link you provided, I get

You are not authorized to view this page
The Web server you are attempting to reach has a list of IP addresses that are not allowed to access the Web site, and the IP address of your browsing computer is on this list.

Please try the following:

* Contact the Web site administrator if you believe you should be able to view this directory or page.

HTTP Error 403.6 - Forbidden: IP address of the client has been rejected.
Internet Information Services (IIS)

RoniVered said...

Your post was useful even after 4 years :)

I needed to convert my DB to UTF8 and you post helped me a lot. I didn't want to re-create the DB.

Thanks very much !

Fahd said...

Dear Dimitri ,

Link is broken http://dgielis.gg-design.be/blog/characterset_upgrade.zip

Can you please update the link.

Thanks

Dimitri Gielis said...

Hi Fahd,

Here you go : https://s3.amazonaws.com/apexRnD/blog/characterset_upgrade.zip

Dimitri