Unicode migration with an Oracle database

Out of Babylon

Article from ADMIN 13/2013

By Johannes Ahrends

If your operating system and database work with different character sets, migrating to the common denominator, Unicode, is often the solution. In this article, we consider what to watch out for in migrations with Oracle.

Oracle offers several possibilities for migrating to Unicode. However, some of them are omitted (i.e., the "Oracle Database Migration Utility for Unicode," or DMU for short, which Oracle has offered since 2011) in a production scenario, because enterprise requirements do not envisage the necessary downtime or because the system requirements for the product (release and patch status) are not met. In contrast, the procedure presented here migrates very large databases in a very short time.

Why Unicode?

Every database is created with a certain code page. En route from the application to the database, the characters in the data are translated: The application code page is mapped to that of the database. However, this is only the case if you have character data types (CHAR, VARCHAR2, CLOB, LONG, NCHAR, NVARCHAR2, NCLOB), because you do not want to convert binary data, of course.

Conversion takes place automatically in transit (via Oracle Net); for example, a Euro sign typed on a Windows machine (0x80 in WIN-1252) is entered in the database as 0xA4 – if you create the database with the ISO-8859P15 character set. However, if the database was created with a Unicode character set, then the Euro sign is stored as a three-byte sequence (0xE282AC) and therefore requires three times as much space as before.

In the past, most databases in Europe were created with a one-byte character set (i.e., ISO-8859 or MSWIN-1252).

In the course of globalization, companies now increasingly need global databases. Oracle has thus recommended for some time that enterprises use Unicode as the default character set. Many modern applications (e.g., Java applications) also use Unicode by default. The same applies to many of today's operating systems, such as Microsoft Windows 7 or Linux. However, when you're

...

Use Express-Checkout link below to read the full article (PDF).