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:

  1. I'm just testing the comment

    ReplyDelete
  2. Nice tip, thanks.

    ReplyDelete
  3. 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)

    ReplyDelete
  4. 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 !

    ReplyDelete
  5. Dear Dimitri ,

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

    Can you please update the link.

    Thanks

    ReplyDelete
  6. Hi Fahd,

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

    Dimitri

    ReplyDelete