Als PDF Downloaden!

Tipps & Tricks: Dezember 2012
Bereich:
DBA
Erstellung:
12/2012 KS
Versionsinfo:
11.2
Letzte Überarbeitung:
12/2012 KS
Import bei Zeichensatzänderung
Folgendes Szenario kommt Ihnen möglicherweise bekannt vor:
Sie wollen ein Schema oder einzelne Tabellen mit Hilfe der Import-Werkzeuge in eine 11gR2 Datenbank
einspielen. Während des Import-Vorganges könnte folgender Fehler auftreten:
ORA-12899: value too large for column STANDORT (actual: 25, maximum: 24)
ORA-02372: data for row: STANDORT :
0X'556E74657268616368696E6720626569204DFC6E6368656E'
Was ist passiert?
Eine Abfrage auf die Tabellen-Spalte in der Quell-Datenbank zeigt folgendes:
SQL> select standort from consulting;
STANDORT
------------------------Unterhaching bei München
Das Schema oder die Tabelle sind aus einer Datenbank mit Single-Byte-Zeichensatz (z.B. WE8MSWIN1252)
extrahiert worden und sollen jetzt in eine 11gR2 Datenbank mit dem Multibyte-Zeichensatz AL32UTF8
(Empfehlung Oracle) eingespielt werden.
Der Multibyte-Zeichensatz AL32UTF8 stellt Umlaute, sprachenspezifische Zeichen etc. mit mehr als 1 Byte dar,
sodass die Spaltenbreite nicht mehr ausreichend ist. Für den Umlaut 'ü' aus dem Beispiel werden 2 Bytes zur
Speicherung benötigt.
Das ist Ihnen möglicherweise bekannt. Doch was machen, um das Problem zu lösen?
Ein erster Versuch, den Parameter nls_length_semantics von 'BYTE' (default) auf 'CHAR' in der Zieldatenbank zu
setzen:
SQL> alter system set nls_length_semantics='CHAR';
Leider führt das Setzen dieses Parameters (auch auf der Quell-Datenbank!) und der anschließende
(erforderliche!) Neustart der Datenbank nicht zum gewünschten Erfolg.
Die Import-Utilities benutzen die Definitionen der Quell-Datenbank. Das gilt sowohl für das "impdp"- als auch das
herkömmliche "imp"-Utility. Wenn beim Anlegen der Tabellenspalte nicht explizit "CHAR" angegeben wurde (oder
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 3
Die Import-Utilities benutzen die Definitionen der Quell-Datenbank. Das gilt sowohl für das "impdp"- als auch das
herkömmliche "imp"-Utility. Wenn beim Anlegen der Tabellenspalte nicht explizit "CHAR" angegeben wurde (oder
in der Session nls_length_semantics auf 'CHAR' geändert wurde), wird der Längen-Wert "BYTE" von der
Quell-Datenbank übernommen.
Bitte beachten Sie, dass Utilities wie der SQL*Developer in der Voreinstellung (Extras -> Voreinstellungen ->
NLS -> Länge) 'CHAR' verwenden und somit eine Tabellen-Spalte bei Neuanlage mit 'CHAR' z. B. varchar2(24
CHAR) definiert wird.
Ein nachträgliches Setzen von nls_length_semantics auf System- oder Session-Ebene hat keinen Einfluss auf
eine bereits definierte Tabellen-Spalte!
Die Längendefinition der Tabellen-Spalte kann der folgenden Query entnommen werden:
SQL> select char_used from dba_tab_columns where owner='MQS' and
table_name='CONSULTING' and column_name='STANDORT';
C
B
Welche Möglichkeiten haben Sie nun?
Wenn Sie Zugriff auf die Quell-Datenbank haben, können Sie die Längendefinition der Tabellen-Spalte auf den
Wert 'CHAR' ändern und den Export nochmals wiederholen:
SQL> alter table consulting modify (standort varchar2(24 char));
Natürlich können Sie auch die Spaltenbreite mit folgendem Kommando ändern:
SQL> alter table consulting modify (standort varchar2(25));
Häufig haben Sie aber aus administrativen oder organisatorischen Gründen (Stichwort "Change-Management")
oder auch, weil Sie das Export-Dump-File von einem Drittanbieter erhielten, keinen Zugriff auf die
Quell-Datenbank.
Um das Problem zu lösen, sollte folgende Vorgehensweise betrachtet werden:
1) Importieren der Metadaten eines Schemas/einer Tabelle
Beispiel ("impdp"-Utility, für "imp" bitte die entsprechende "IMPORT"-Syntax verwenden):
dumpfile=data_pump_dir:EXPORT_CONSULTING.DMP
logfile=data_pump_dir:import_consulting_meta_data.log
schemas=mqs
content=metadata_only
2) Ändern der Spalten-Definition
SQL> alter table consulting modify (standort varchar2(24 char));
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 3
Diese Methode ist bei wenigen Tabellen-Spalten, die betroffen sind, gut einsetzbar. Wenn Sie allerdings ein
Schema mit sehr vielen Tabellen importieren wollen, kann dies zu einer aufwendigen und fehleranfälligen
Prozedur werden.
Besser ist die in MOS-Artikel 313175.1 beschriebene Methode. Wenn Sie keine partitionierten Tabellen und keine
auf den zu ändernden Spalten liegende Function_Based Indizes haben, können Sie direkt das in Abschnitt D)
enthaltene Skript zum Ändern aller auf 'BYTE' stehenden Tabellen-Spalten verwenden. Sie müssen dabei nur die
Schema-Owner ändern.
Der Output unserer Tabelle würde folgendermaßen aussehen:
Starting build select of columns to be altered
ALTERing 1 columns in 1 tables
ALTER TABLE "MQS"."CONSULTING" modify ("STANDORT" VARCHAR2(24 CHAR))
Done
3) Import der Rows
Beispiel:
dumpfile=data_pump_dir:EXPORT_CONSULTING.DMP
logfile=data_pump_dir:import_consulting.log
schemas=mqs
content=data_only
Fazit:
Zur Vermeidung der beschriebenen Problematik ist es sinnvoll, bei Neuanlage von Schemata oder Tabellen den
Session-Parameter 'nls_length_semantics' auf 'CHAR' zu setzen.
Zur Abfrage des akuellen Session-Parameters die folgende Query benutzen:
select value from nls_session_parameters where parameter='NLS_LENGTH_SEMANTICS';
VALUE
--------------------------------------------------------------------BYTE
Den Wert 'CHAR' folgendermaßen setzen:
alter session set nls_length_semantics='CHAR';
Session altered.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 3