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.