News
DBA-Tipp: SQL Export einer Oracle Datenbank

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Stellt sich einem die Aufgabe einzelne Objekte einer Oracle Datenbank im SQL Code zu extrahieren bieten sich hierfür, je nach Objekt-Typ, mehrere Möglichkeiten.

Der Quelltext von PL/SQL Konstrukten wie Packages, Trigger, Functions etc. findet sich, je nach Geltungsbereich, in den Views dba_source, all_source bzw. user_source. Der Geltungsbereich gliedert sich dabei wie bei den meisten Views des Data Dictionaries in sämtliche in der Datenbank vorhandene Objekte (dba_source), alle Objekte die für den aktuellen Nutzer "sichtbar" sind, auf die er also zugreifen kann (all_source) sowie sämtliche Objekte die dem aktuellen Nutzer gehören (user_source).

Um sich zum Beispiele den Quelltext des Package FOO.PKG_MAIN ausgeben zu lassen kann man folgende Abfrage verwenden:

SQL> SELECT text FROM DBA_SOURCE WHERE owner='FOO' AND name='PKG_MAIN' ORDER BY line;
TEXT
--------------------------------------------------------------
PACKAGE pkg_main as
PACKAGE body pkg_main as

    procedure add_policy(user_name in varchar2, table_name in varchar2) is
[...]

Ähnlich verhält es sich mit Views. Für diese Objekte existieren die Data Dictionary Views dba_views, all_views und user_views. Die Abfrage unterscheidet sich nur marginal von der vorherigen, in dem Beispiel wird die Beschreibung der View NICE_VIEW des Nutzers FOO ausgegeben:

SQL> SET LONG 99999
SQL> SELECT text FROM dba_views WHERE owner='FOO' AND view_name='NICE_VIEW';
TEXT
--------------------------------------------------------------
select "ID" from stuff
SQL>

Der oben beschriebene Ansatz ist jedoch lediglich für PL/SQL Objekte wie Packages, Procedures und Functions sowie Views und Materialized Views praktikabel.

Eine universellere Methode bietet das Package DBMS_METADATA. Dieses bietet mit der Funktion GET_DDL die Möglichkeit die SQL Repräsentanz sämtlicher Datenbank-Objekt-Typen zu erstellen. Dazu gehören neben den logischen Eigenschaften auch die Speicherparameter wie etwa der zugeordnete Tablespace, der Buffer Pool, die Dimensionierung der Extents etc.

Den SQL Code des Constraints FOO.PK_STUFF erhält man etwa wie folgt:

SQL> SELECT dbms_metadata.get_ddl('CONSTRAINT','PK_STUFF','FOO') FROM DUAL;

DBMS_METADATA.GET_DDL('CONSTRAINT','PK_STUFF','FOO')
--------------------------------------------------------------

ALTER TABLE "FOO"."STUFF" ADD CONSTRAINT "PK_STUFF" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE

Will man sich nicht auf einzelne Objekte oder Objekt-Typen beschränken, sondern den SQL Code ganzer Schemata generieren, kann man sich mit Data Pump behelfen. Das Import-Tool impdp bietet mit dem Parameter sqlfile die Möglichkeit einen Dump nicht in die Datenbank einzuspielen, sondern die entsprechenden Metadaten als SQL Code in eine Textdatei auszugeben:

$ impdp system schemas=FOO directory=DATA_PUMP_DIR dumpfile=foo.dmp sqlfile=foo.sql

Will man sich den Umweg ersparen, das Schema oder die komplette Datenbank dafür extra mit expdp exportieren zu müssen, kann man auf das Package DBMS_DATAPUMP zurückgreifen. Damit lassen sich DataPump Ex- und Import Jobs aus der Datenbank heraus erstellen und steuern. Dabei erfolgt der Datenzugriff, wie mit expdp / impdp auch, über Dumpfiles oder Database Links. Das Anlegen eines rekursiven Database Links zum Zugriff auf die lokale Datenbank kann ausbleiben. Statt des Namens eines Database Links (zum Zugriff auf eine entfernte Datenbank) kann einfach der Name der lokalen Datenbank (hier ORCL) gewählt werden. Somit lässt sich der SQL Code ganzer Schemata generieren, hier z.B. für das Schema FOO:

SQL> DECLARE
  dp_job_handle number;
BEGIN
dp_job_handle := DBMS_DATAPUMP.OPEN('SQL_FILE', 'SCHEMA', 'ORCL', 'SQL_EXPORT_FOO', 'COMPATIBLE');
DBMS_DATAPUMP.ADD_FILE (dp_job_handle, 'foo.sql','DATA_PUMP_DIR', NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE);
DBMS_DATAPUMP.METADATA_FILTER(dp_job_handle,'SCHEMA_EXPR','IN (''FOO'')');
DBMS_DATAPUMP.START_JOB (dp_job_handle);
DBMS_DATAPUMP.DETACH (dp_job_handle);
END;
/

Nach erfolgreichem Export beinhaltet das Textfile foo.sql in dem Standard Database Directory DATA_PUMP_DIR den SQL Code des Schemas FOO.

Leider beschränken sich die hier vorgestellten Methoden lediglich auf den Export der Metadaten. Zum reinen Datenexport führt an Data Pump kein Weg vorbei. In Einzelfällen kann für den SQL Export jedoch auch auf den SQL Developer zurückgegriffen werden, allerdings erlaubt dieser auch nur den Export einzelner Objekte und nicht ganzer Schemata.