Wie in dem Blog “Zeichensatzkonvertierung mit CSALTER – funktioniert das wirklich?” beschrieben, ist es zwar möglich, mit CSSCAN bzw. CSALTER den Zeichensatz der Datenbank zu ändern, aber das hilft nicht, wenn man, wie in Europa wohl üblich, mehr als US7ASCII Zeichen in der Datenbank speichert, da nur das Data Dictionary angepasst wird, nicht jedoch die Daten in den Tabellen.
Also bleibt mir wohl nichts anderes übrig, als die Daten aus der WE8-Datenbank herauszuladen und in eine neue Unicode Datenbank zu importieren. Als Zeichensatz für das Zielsystem verwende ich wieder AL32UTF8, d.h. ein Zeichensatz mit mindestens einem Byte (UTF8) und maximal 4 Byte (AL32). Für die deutschen Zeichen bedeutet das, dass ein Umlaut (Ä,Ö,Ü 2 Byte benötigt und das “ß” sogar 3 Byte).
1. Versuch: Export / Import im DataPump
Da es sich bei meinen Testdatenbanken um Oracle 11g Release 2 (11.2.0.2) auf meinem Lieblingsbetriebssystem OEL 6 handelt, werde ich natürlich Data Pump benutzen. Für ältere Systeme funktioniert das aber ähnlich mit dem klassischen exp / imp.
Zunächst werden die Schemata (DEMO und DEMOSTAMM) auf dem Quellsystem exportiert und anschließend auf dem Zielsystem importiert. Das verwendete Directory-Objekt “DATA_PUMP_DIR” wird übrigens bei Oracle 11g Release 2 automatisch bei der Installation der Datenbank angelegt und zeigt auf das Verzeichnis $ORACLE_BASE/admin/<ORACLE_SID>/dpdump
Parameterdatei Export:
DUMPFILE="demoexpwe8.dmp"
LOGFILE="exp_demoexpwe8.log"
DIRECTORY=DATA_PUMP_DIR
CONTENT=ALL SCHEMAS=('DEMOSTAMM', 'DEMO')
Parameterdatei Import:
DUMPFILE="demoexpwe8.dmp"
LOGFILE="imp_demoimputf.log"
DIRECTORY=DATA_PUMP_DIR
STREAMS_CONFIGURATION=n
TABLE_EXISTS_ACTION=APPEND
SKIP_UNUSABLE_INDEXES=y
CONTENT=ALL
PARTITION_OPTIONS=none
SCHEMAS=('DEMOSTAMM', 'DEMO')
Der Log vom Import sieht jetzt so aus:
...
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** parfile=demoimputf.dat
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DEMO"."POSITIONEN" 727 KB 30267 rows
ORA-02374: conversion error loading table "DEMO"."AUFTRAEGE"
ORA-12899: value too large for column AUFSTATUS (actual: 2, maximum: 1)
ORA-02372: data for row: AUFSTATUS : 0X'DC'
ORA-02374: conversion error loading table "DEMO"."AUFTRAEGE"
ORA-12899: value too large for column AUFSTATUS (actual: 2, maximum: 1)
ORA-02372: data for row: AUFSTATUS : 0X'DC'
...
. . imported "DEMO"."AUFTRAEGE" 299.3 KB 9934 out of 10000 rows
. . imported "DEMOSTAMM"."NACHNAMEN" 177.4 KB 11552 rows
...
ORA-02374: conversion error loading table "DEMO"."STATUS"
ORA-12899: value too large for column STATUSID (actual: 2, maximum: 1)
ORA-02372: data for row: STATUSID : 0X'DC'
. . imported "DEMO"."STATUS" 6.093 KB 5 out of 6 rows
...
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (DEMO.FK_POSITIONEN_AUFTRAEGE) - parent keys not
found
Failing sql is: ALTER TABLE "DEMO"."POSITIONEN" ADD CONSTRAINT "FK_POSITIONEN_AUFTRAEGE" FOREIGN KEY ("AUFID") REFERENCES
"DEMO" ."AUFTRAEGE" ("AUFID") ON DELETE CASCADE ENABLE
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 14:58:03
Was ist passiert?
Nun: wenn man sich die Fehlermeldung ansieht, stellt man fest, dass “0X’DC‘” für den Umlaut “Ü” im Unicode Zeichensatz steht. Die Fehlermeldung “ORA-12899: value too large for column AUFSTATUS (actual 2, maximum: 1)” liefert den Grund: die Spalte Auftragsstatus wurde mit einer maximalen Länge von einem Byte definiert – und der Umlaut, der bei dem WE8 Zeichensatz nur ein Byte belegt, passt jetzt nicht mehr, weil er jetzt zwei Byte belegt.
Damit ist auch dieser Versuch gescheitert!
Lösung:
Die Tabellen müssen, angepasst auf die neue Zeichenlänge angepasst werden. Dabei könnte man jetzt alle Felder entsprechend verlängern, also aus CHAR(1) z.B. CHAR(3) machen (wegen “ß“). Aber Oracle hält seit Version 9i die Möglichkeit bereit, die Länge eines Character-Feldes nicht mehr in “BYTE” sondern in “CHAR” anzugeben. Dabei spricht man von der Length-Semantic.
Leider stellt SQL*Plus beim DESCRIBE keine Längensemantik dar, also muss man auf die Dictionary Views ausweichen.
Ein Beispiel:
NLS_LENGTH_SEMANTIC=BYTE
SELECT column_name,
data_type,
data_length,
decode(char_used,'B','BYTE','C','CHAR') Semantic
FROM user_tab_columns
WHERE table_name = 'STATUS'
COLUMN_NAME DATA_TYPE DATA_LENGTH SEMANTIC
------------------------------ ---------- ----------- ----------
STATUSID CHAR 1 BYTE
KURZBESCHREIBUNG VARCHAR2 10 BYTE
BESCHREIBUNG VARCHAR2 255 BYTE
In einer anderen Datenbank mit NLS_LENGTH_SEMANTIC=CHAR
COLUMN_NAME DATA_TYPE DATA_LENGTH SEMANTIC ------------------------------ ---------- ----------- ---------- STATUSID CHAR 4 CHAR KURZBESCHREIBUNG VARCHAR2 40 CHAR BESCHREIBUNG VARCHAR2 1020 CHAR
Das Problem dabei ist, dass Data Pump und auch das klassische Export / Import die Semantic “mitnehmen”. D.h. wenn die Tabellen mit BYTE angelegt worden sind, werden Sie auch in der neuen Datenbank, unabhängig wie die Längensemantic, die auf Systemebene (ALTER SYSTEM) oder Sessionebene (ALTER SESSION) eingestellt werden kann, gesetzt ist.
Also muss zunächst die Tabellendefinition extrahiert werden. Dabei ist darauf zu achten, dass die Längensemantic nicht mit extrahiert wird. Mit der Funktion dbms_metadata.get_ddl können diese Definitionen als SQL-Befehl erstellt werden. Jetzt reicht es, auf dem Zielsystem die Längensemantic auf “CHAR” zu setzen und die Tabellen zu erstellen.
Beispiel für ein SQL*Plus Skript:
set heading off
set feedback off
set trimspool ON
set pages 0
set linesize 1000
set long 2000000
spool create_tables.sql
PROMPT ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR
prompt /
--
-- keine alten Storageklauseln (MINEXTENTS etc.)
--
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE',FALSE);
--
-- ";" als Trennzeichen zwischen Befehlen
--
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',TRUE);
--
-- Segmentattribute wie PCTFREE, etc. übernehmen (ist Default)
--
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES',TRUE);
SELECT DBMS_METADATA.GET_DDL ('TABLE' ,T.table_name,T.owner)
FROM dba_tables T
WHERE owner IN ('DEMO','DEMOSTAMM');
spool off
Das erstellte SQL*Plus Skript create_tables.sql sieht jetzt so aus:
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR
/
...
CREATE TABLE "DEMO"."STATUS"
( "STATUSID" CHAR(1) CONSTRAINT "SYS_C003083" NOT NULL ENABLE,
"KURZBESCHREIBUNG" VARCHAR2(10),
"BESCHREIBUNG" VARCHAR2(255),
CONSTRAINT "PK_STATUS" PRIMARY KEY ("STATUSID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
...
Das “ALTER SESSION” ist direkt mit eingebaut worden. Somit kann das Skript direkt auf dem Zielsystem ausgeführt werden.
Danach kann dann wieder ein DataPump Import erfolgen, es ist nur darauf zu achten, dass der Parameter TABLE_EXISTS_ACTION auf APPEND oder TRUNCATE und nicht auf SKIP gesetzt ist, damit die Daten tatsächlich eingefügt werden. Außerdem müssen in diesem Fall die Schemata vorher angelegt werden.
Aber leider schlägt auch dieser Import fehl!
Grund hierfür ist, dass bei der Erstellung der Objekte die Contraints (vor allen Dingen Foreign-Keys) angelegt und aktiviert worden sind. Ein anschließender Import der Daten birgt die Gefahr, dass die Reihenfolge des Einfügens nicht der Master – Detail Beziehung entspricht und dadurch “ORA-02291 integrity contraint violated – parent key not found” Fehler auftreten.
Um also den endgültigen Import durchführen zu können, müssen vorher alle Foreign-Key Constraints disabled werden.
Am Besten macht man sich wieder ein kleines SQL*Plus Skript, dass zwei weitere Skripte erzeugt: disable_cons.sql und enable_cons.sql.
set heading off
set feedback off
set trimspool ON
set pages 0
set linesize 1000
spool disable_cons.sql
SELECT 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||';'
FROM all_constraints
WHERE owner IN ('DEMO','DEMOSTAMM')
AND constraint_type='R';
spool off
spool enable_cons.sql
SELECT 'ALTER TABLE '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||';'
FROM all_constraints
WHERE owner IN ('DEMO','DEMOSTAMM')
AND constraint_type='R';
spool off
Damit kann jetzt endgültig der Import der Daten in die Zieldatenbank erfolgen.
Dieses Trial and Error kann man natürlich nur auf einem Testsystem durchführen, auf dem man sicher sein kann, dass es einen identischen Datenbestand zur Produktion gibt. Testdaten, verschlüsselte oder ungültig gemachte Daten bergen hierbei die Gefahr, dass Fehler aufgrund der Zeichenlänge nicht erkannt werden. Im Beispiel war das noch sehr offensichtlich, da CHAR(1 BYTE) förmlich nach einem Fehler bei der Umstellung schreit.
Schwieriger wird es, wenn die Länge eines Felder bei mehreren 100 BYTE liegt oder, noch schwerwiegender, wenn die maximale Größe von VARCHAR2 erreicht wird. Diese liegt nämlich immer noch bei 4000 BYTE und nicht etwa bei 4000 CHAR. Das ist eine harte Grenze, die nur durch die Änderung in ein CLOB Feld gelöst werden kann.
CLOB = UCS-2
Noch ein Wort von CLOB: Beim Wechsel auf AL32UTF8 wird für CLOB immer der UCS-2 Zeichensatz verwendet, d.h. jedes Zeichen wird 2 (!) Byte lang. Daher sollten Sie beim Umstieg auf Unicode überlegen, welche Auswirkungen dass auf ihre Tabellen hat, die mit CLOB-Feldern haben.
Hier der Hinweis aus der Oracle Dokumentation (Globalization Support Guide, Kapitel 6):
Data in CLOB columns is stored in a format that is compatible with UCS-2 when the database character set is multibyte, such as UTF8 or AL32UTF8. This means that the storage space required for an English document doubles when the data is converted. Storage for an Asian language document in a CLOB column requires less storage space than the same document in a LONG column using UTF8, typically around 30% less, depending on the contents of the document.
Im nächsten Blog werde ich Ihnen Möglichkeiten aufzeigen, wie man diese Prozdur vereinfachen kann (z.B: durch die Verwendung meines Lieblingstools Toad) und wie die Auszeit der Anwendung minimiert werden kann.