Umstellung des Zeichensatz von WE8MSWIN1252 auf UTF8
Viele „alte“ Oracle Datenbanken laufen noch mit der Kodierung WE8MSWIN1252. Wenn nun die Internationalisierung stärker in den Fokus der Entwicklung kommt, werden schnell die Grenzen deutlich, da viele Sprachen mit der Kodierung WE8MSWIN1252 nicht in der Datenbank abgebildet werden können.
Die heute übliche Lösung ist die Umstellung der Datenbank auf UTF(8). Wie viele andere administrative Aufgaben ist auch diese bei Oracle Systemen leider nicht trivial und schlecht oder gar nicht dokumentiert. Dieser Blog beschreibt ein Vorgehen, das bei uns für eine Oracle 11 Datenbank funktioniert hat. Je nach den aktuell in der Datenbank verwendeten Datentypen können weitere Schritte notwendig sein. Folgende Schritte werden im Folgenden beschrieben:
- Anpassung von großen Varchar2 Elementen auf CLOB
- Erstellen eines Datenbanksicherung in der Kodierung UTF8
- Umstellen der Datenbank auf UTF8
- Anpassen des Schemas für Varchar2 Elemente
- Import der Tabelleninhalte
Umstellung von großen Varchar2 Attributen auf CLOB
Ein Zeichen in UTF-8 benötigt mehrere Bytes Speicherplatz im Gegensatz zu einem Zeichen in WE8MSWIN1252, welches lediglich ein Byte benötigt. In Oracle werden Längen von Varchar2 Attributen, wenn keine weiteren Angaben gemacht werden, in Byte angegeben. Ein Varchar2(18) Element kann also nicht 18 Zeichen verwalten, sondern 18 Byte. Da Oracle 3 Byte je UTF8 Zeichen benötigt, können in einem solchen Feld nach der Umstellung lediglich 6 Zeichen gespeichert werden.
Die Umstellung von kleineren Varchar2-Attributen wird weiter unten beschrieben und erfolgt innerhalb der neuen Datenbank. Problematisch sind Varchar2-Attribute, die in der Ausgangsdatenbank größer als 1333 Zeichen (Achtung hier sind wirklich Zeichen gemeint) sind. Diese Elemente würden nach der Umwandlung mehr als 4000 Byte benötigen Die maximale Länge eines Varchar2 in Oracle liegt allerdings nur bei 4000 Byte.
Daher müssen solche Felder vor den weiteren Schritten in Clobs umgewandelt werden.
- Ausgangsattribut löschen
- Clob Attribut anlegen
Die folgende SQL-Anweisung erzeugt ein Skript, um die Varchar2 Attribute mit einer Größe von über 1333 in clob Felder umzusetzen:
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP COLUMN ' || COLUMN_NAME || ';' || CHR(13) || 'ALTER TABLE ' || TABLE_NAME || ' ADD ' || COLUMN_NAME || ' CLOB;' FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2' AND DATA_LENGTH > 1333;
Die Ergebniszeilen werden als Skript ausgeführt.
Erstellen eines DB-Dumps mit UTF8 Zeichenkodierung
Nachdem nun alle Vorbereitungen getroffen wurden, kann nun eine Sicherung des Datenbestands erstellt werden. Beim Export wird das „normale“ Dump Kommando verwendet und die Zeichenkodierung mit der Umgebungsvariable
export NLS_LANG=.UTF8
auf UTF8 gestellt.
Umstellen der Datenbank auf UTF8
In dem Wiki Beitrag
https://de.wikibooks.org/wiki/Oracle:_CharacterSet_%C3%A4ndern
ist die (interne) Umstellung von Oracle 11 auf UTF 8 beschrieben. Die folgende Auflistung enthält die Schritte, die bei der in diesem Blog verwendeten Oracle 11 Version notwendig waren:
- Als Nutzer sys mit sysdba-Berechtigung via sqlplus an der Oracle-Instanz anmelden. sqlplus „sys/password@orcl as sysdba“
- CharacterSet der Datenbank überprüfen select value from nls_database_parameters where parameter=’NLS_CHARACTERSET‘; bzw. um sämtliche Einstellungen zu sehen select * from nls_database_parameters;
- Die Datenbank anhalten shutdown immediate;
- Bei der verwendeten Versionen gibt es einen Bug, bei dem ein Fehler beim Ausführen von „startup mount;“ ausgegeben wird. Daher vorher folgenden Befehl ausführen: connect / as sysdba;
- Die Datenbank einhängen aber noch nicht öffnen startup mount;
- Session für Operation vorbereiten alter system enable restricted session; alter system set job_queue_processes=0;
- Nun die Datenbank öffnen alter database open; und den CharacterSet ändern alter database character set internal_use utf8;
- Datenbank wieder schließen und herunterfahren shutdown immediate;
- Datenbank normal hochfahren startup;
- Nochmals die aktuellen Werte prüfen select value from nls_database_parameters where parameter=’NLS_CHARACTERSET‘;
- Damit kann SqlPlus wieder beendet werden exit;
Anpassen des Schemas für Varchar2 Felder
Mit dem folgenden SQL-Befehl wird ein Skript erzeugt, um die verbliebenen Varchar2 Attribute der Tabellen so zu erweitern, dass sie UTF8-Kodierte Zeichen aufnehmen zu können.
SELECT 'ALTER TABLE ' || TABLE_NAME || ' MODIFY ' || COLUMN_NAME || ' VARCHAR2(' || DATA_LENGTH || ' CHAR);'
FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'VARCHAR2';
Die Ergebniszeilen werden als Skript ausgeführt. Wenn in der Datenbank Char Attribute existieren, muss ein analoges Vorgehen für diese verwendet werden.
Import der Tabelleninhalte
Da wir bisher nur die Datenbank auf UTF-8 umgestellt haben, die Daten aber nicht verändert wurden, würden bei der Nutzung des aktuellen Standes z.B. alle Umlaute falsch angezeigt werden. Um dies zu bereinigen, müssen die Daten aus dem oben erstellten UTF-8 Datenbank-Dump eingespielt werden. Dies erfolgt mit folgenden Schritten:
- Deaktivieren der Constraints der Datenbank
- Erzeugung eines Scripts, um die Inhalte der Tabellen zu löschen
- Ausführung des erzeugten Scripts zum Löschen der Inhalte
- Import der Inhalte
- Reaktivierung der Constraints.
Constraints deaktivieren
Mit der folgenden SQL-Anweisung wird ein Skript erzeugt, das die Constraints der Datenbank daktiviert:
SELECT 'alter table '|| table_name||' disable constraint '||CONSTRAINT_NAME||';' from user_constraints where table_name In (SELECT table_name from user_tables);
Die erzeugten Ergebniszeilen werden als Skript ausgeführt.
Löschen der Tabelleninhalte
Die Inhalte der Tabellen werden mit den Ergebnissen der folgenden Anweisung gelöscht
SELECT 'TRUNCATE TABLE ' || table_name || ';' from user_tables;
Einspielen der Tabelleninhalte aus dem Dump
Die zu Beginn gesicherten Daten in UTF-8 Kodierung werden nun eingespielt. Wichtig sind hierbei die Parameter FULL=Y IGNORE=Y CONSTRAINTS=N und das Setzen der Umgebungsvariable export NLS_LANG=.UTF8.
#!/bin/bash ... export NLS_LANG=.UTF8 ... $IMP $USER/$PASS@orcl FILE=$FILENAME LOG=$LOG FULL=Y IGNORE=Y CONSTRAINTS=N ...
Constraints aktivieren
Nachdem die Daten wieder eingespielt wurden, müssen nun die Constraints aktiviert werden. Das folgende SQL-Kommando erstellt ein Skript, um die Constraints der Datenbank wieder zu aktivieren:
SELECT 'alter table '|| table_name||' enable constraint '||CONSTRAINT_NAME||';' from user_constraints where table_name In (SELECT table_name from user_tables);
Die Ergebniszeilen müssen als Skript ausgeführt werden.
Zusammenfassung
Mit dem obigen Vorgehen wurde eine Datenbank auf UTF-8 umgestellt. Da Oracle die Änderung von Kodierungen nicht unterstützt, sind sehr viele manuelle Schritte notwendig. Für einen der Marktführer im Datenbank-Sektor ist dies aus meiner Sicht nicht verständlich.