Import bei Zeichensatzänderung

04.
Dezember
2012
Veröffentlicht von: Kurt Sauer

Folgendes Szenario kommt Ihnen möglicherweise bekannt vor:

 

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 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));


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.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.