11. Januar 2010

Data Pump Exports per PL/SQL anstoßen ...

English title: Export operations with PL/SQL and DBMS_DATAPUMP

Wusstet Ihr schon, dass Ihr mit Export-Vorgänge auch per PL/SQL auslösen könnt? Ab Oracle10g wurde ja die Data Pump als servergestütztes Export/Import-Werkzeug eingeführt. Der wesentliche Unterschied zum "klassischen" Export/Import ist, dass die Daten in ein Verzeichnis auf dem Server geschrieben bzw. von diesem gelesen werden. Darüber hinaus gibt es gegenüber dem alten Export/Import zahlreiche Verbesserungen und Erweiterungen. Wichtig ist, dass die Dumpfiles der Data Pump inkompatibel zu denen des "klassischen" Export/Import sind. Man kann also ein "klassisches" Dumpfile nicht mit der Data Pump verarbeiten und ein Data Pump Dumpfile nicht mit den "klassischen" Export/Import-Werkzeugen.
Did you know that export and import operations can be done with PL/SQL programming? Oracle10g introduced Data Pump, the new export/import utility. The most important difference between Data Pump and the "classic" export/import is that Data Pump is a server-based utility - that means the export or import operation is being performed by the database server and not by a client utility. Beyond this Data Pump contains several enhancements. It is important to know that the file format of Data Pump is not compatible with the format of the "classic" export/import utilities. So you can neither process a "classic" dumpfile with Data Pump nor process a Data Pump dumpfile with the "classic" utilities "exp" and "imp".
Neben den Kommandozeilenwerkzeugen expdp und impdp (analog zum "klassischen" exp und imp) gibt es bei der Data Pump auch ein PL/SQL-Paket - der Vorgang findet ja, wie gesagt, serverseitig statt. Dieses Paket DBMS_DATAPUMP wird letztlich auch expdp und impdp aufgerufen. Man kann es allerdings auch aus der PL/SQL-Ebene heraus verwenden. Und in Verbindung mit dem Job-Scheduler der Datenbank ist es ein Leichtes, bestimmter Export-Dateien jobgesteuert zu generieren ...
Data Pump provides command line utilities (similar to "classic" export/import). Those tools are expdp and impdp. But there is also a PL/SQL package DBMS_DATAPUMP - as said: all operations are being performed by the database server. The command line tools also use the PL/SQL package. But the interesting bit is that you can also use this package from the SQL and PL/SQL level. So one can generate a table export via a PL/SQL procedure - this procedure could then be called regularly with DBMS_SCHEDULER.
Zunächst wird ein Directory-Objekt benötigt; die Data Pump arbeitet ja serverseitig.
The export dumpfile is always generated as a plain file in the server's filesystem. So we need a directory object as the first step.
create directory dp_test_dir as '/home/oracle'
/
Dann kommt eine PL/SQL-Prozedur, die den Dump mit Hilfe von DBMS_DATAPUMP generiert. Wenn Ihr das selbst ausprobieren möchtet, ist die Package Reference vielleicht ganz interessant für euch.
The next step is the PL/SQL procedure which generates the dumpfile using DBMS_DATAPUMP. You might have a look into the package reference.
create or replace procedure export_table(p_table_name in varchar2) authid current_user
is
  v_dp_handle number;
  v_tname     varchar2(30) := p_table_name;
  pragma AUTONOMOUS_TRANSACTION; 
begin
  -- Create Data Pump Handle - "TABLE EXPORT" in this case
  v_dp_handle := dbms_datapump.open(
    operation      => 'EXPORT',
    job_mode       => 'TABLE'
  );
  -- Specify target file - make it unique with a timestamp
  dbms_datapump.add_file(
    handle         => v_dp_handle,
    filename       => v_tname || '_'||to_char(sysdate, 'YYYYMMDD-HH24MISS')||'.dmp',
    directory      => 'DP_TEST_DIR'
  );
  -- Export only those tables specified in the procedure's parameter
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'NAME_EXPR',
    value          => '='''|| v_tname ||''''
  );
  -- Do it!
  dbms_datapump.start_job(
    handle        => v_dp_handle
  );
  commit;
end;
/
Das COMMIT am Ende der Prozedur ist sehr wichtig. Die Data Pump API erwartet, das die Prozedur in einer autonomen Transaktion abläuft und diese muss am Ende "Committed" werden. Diese Prozedur lässt sich nun beliebig verwenden - sie kann explizit aufgerufen werden (bspw. aus Application Express heraus) oder regelmäßig als Scheduler Job gestartet werden. Das folgende Code-Beispiel sorgt nun dafür, dass die Tabelle EMP einmal am Tag exportiert wird. Um das Einrichten des Jobs zu vereinfachen habe ich noch eine Prozedur hinzugefügt: Diese wird ohne Parameter aufgerufen und ruft die Original-Prozedur für die Tabelle "EMP" auf.
The COMMIT at the end of the procedure is important. The Data Pump API needs to run in an autonomous transaction (see the "PRAGMA" definition at the beginning) and this transaction has to be committed at the end. Such a procedure can now be called as you like: Using it in an APEX Web GUI is possible as well as calling it regularly with DBMS_SCHEDULER as the following code shows. To simplify the job creation I added another procedure (without parameters) which calls the original procedure for the EMP table.
create or replace procedure exp_table_sched 
is
begin
  export_table('EMP');
end;
/

begin
  dbms_scheduler.create_job(
    job_name            => 'DUMP_TABLE_EMP',
    job_type            => 'stored_procedure',
    job_action          => 'EXP_TABLE_SCHED',
    number_of_arguments => 0,
    start_date          => to_timestamp('2010-01-10 20:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    repeat_interval     => 'FREQ=DAILY; INTERVAL=1',
    end_date            => null,
    job_class           => 'DEFAULT_JOB_CLASS',
    enabled             => true,
    auto_drop           => true,
    comments            => null
  );
end;
/
Nachgelagerte Prozesse können diese Datei nun vararbeiten. Analog dazu können nun auch Import-Prozesse auf diesem Weg automatisiert werden (das schauen wir uns dann das nächste Mal an). Nützlich ist sowas für asynchrone Datenverteilung - dort wo das Übertragen per Database Link nicht ausreicht. Anbieter von Daten (bspw. Geodaten) können sowas sicherlich gut nutzen, um ein Dataset automatisiert bereitzustellen.
The resulting file can the be processed further. You can also implement IMPORT processes similar to this approach (we'll look into this in the next blog posting). This could be useful for data distribution processes where replication techniques cannot be applied (beyond corporate boundaries). Another usecase might be the process of generating a dataset - which is done regularly by data providers (say: spatial data providers).

Kommentare:

Daniel Wetzler hat gesagt…

Hallo,

ich hatte neulich auch mit der Thematik zu tun. Dabei habe ich versucht ein Schema ohne Datensätze und komprimiert zu exportieren.

Das sah folgendermassen aus :

dump_job_id := 0;
percent_completed := 0;
JobState := 'UNDEFINED';
dumpfilename := 'ecs3rtdbwodata_'||to_char( sysdate, 'dd_mm_yyyy_HH24_mi_ss') ||'.dmp';
logfilename := 'ecs3rtdbwodata_'||to_char( sysdate, 'dd_mm_yyyy_HH24_mi_ss') ||'.log';

JobHandle :=
DBMS_DATAPUMP.OPEN(
operation => 'EXPORT'
,job_mode => 'SCHEMA'
,remote_link => NULL
--,job_name => 'ECS3RTDB' ||to_char( sysdate, 'dd_mm_yyyy_HH24_mi_ss')
,job_name => dumpfilename
,version => 'LATEST'
);
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => dumpfilename
,directory => 'data_pump_dir'
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
,filesize => '1000M'
);
DBMS_DATAPUMP.ADD_FILE(
handle => JobHandle
,filename => logfilename
,directory => 'data_pump_dir'
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);


dbms_datapump.metadata_filter(JobHandle,'SCHEMA_EXPR','= ''ECS3RTDB''');
DBMS_DATAPUMP.DATA_FILTER (JobHandle, 'INCLUDE_ROWS', 0, 'ECS3RTDB');

DBMS_DATAPUMP.SET_PARAMETER (JobHandle,'COMPRESSION',5);
DBMS_DATAPUMP.START_JOB(JobHandle);

(u.s.w.)

Leider erhalte ich dann folgende Fehlermeldung, die ich nicht gelöst bekomme :

Fehler beim Start in Zeile 1 in Befehl:
exec ECS_EXPORTSCHEMA.EXPORT_ECS3RTDBSCHEMAWODATA
Fehlerbericht:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2953
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3528
ORA-06512: at "ECS_ADMIN.ECS_EXPORTSCHEMA", line 158
ORA-06512: at line 1
39001. 00000 - "invalid argument value"
*Cause: The user specified API parameters were of the wrong type or
value range. Subsequent messages supplied by
DBMS_DATAPUMP.GET_STATUS will further describe the error.
*Action: Correct the bad argument and retry the API.

Die Fehlermeldung bezieht sich auf die Zeile in der ich die Datensätze ausfiltern möchte also :

dbms_datapump.metadata_filter(JobHandle,'SCHEMA_EXPR','= ''ECS3RTDB''');
DBMS_DATAPUMP.DATA_FILTER (JobHandle, 'INCLUDE_ROWS', 0, 'ECS3RTDB');

Können Sie mir dabei weiterhelfen ?

Carsten Czarski hat gesagt…

Hallo Herr Wetzler,

hmmmm ...

dbms_datapump.metadata_filter(JobHandle,'SCHEMA_EXPR','= ''ECS3RTDB''');
DBMS_DATAPUMP.DATA_FILTER (JobHandle, 'INCLUDE_ROWS', 0, 'ECS3RTDB');

Die Tabelle heißt genauso wie das Schema ... wirklich ...?

Auf welche der beiden Zeilen bezieht sich die Fehlermeldung genau ...?

Grüße

-Carsten Czarsju

Daniel Wetzler hat gesagt…

Also der Code der in der in der Fehlermeldung angegebenen Zeile steht ist :

dbms_datapump.metadata_filter(JobHandle,'SCHEMA_EXPR','= ''ECS3RTDB''');

Aber der ist auch vorhanden, wenn ich untenstehedne Zeilen weglasse und das Programm läuft.

Ich hatte vor das gesamte Schema zu exportieren und für alle Tabellen den Filter für 0 Zeilen exportieren anzugeben.

Ich habe nun einmal versucht das nur für eine Tabelle zu machen, was aber zur gleichen Fehlermeldung wie bisher führte.

DBMS_DATAPUMP.DATA_FILTER (JobHandle, 'INCLUDE_ROWS', 0, 'SIGFACTSOPHASE');

Haben Sie eine Idee, wie ich die Daten aus meinem Dump herauslassen kann ?


Viele Grüße,

Daniel

Carsten Czarski hat gesagt…

Hallo,

und wie wäre es, wenn Sie die Tabelle beim DATA_FILTER einfach weglassen ... also

DBMS_DATAPUMP.DATA_FILTER (JobHandle, 'INCLUDE_ROWS', 0);

Beste Grüße

-Carsten Czarski

Daniel Wetzler hat gesagt…

.. dann funktioniert es ganz plötzlich ;-).

Vielen Dank !


Viele Grüße,

Daniel Wetzler

Beliebte Postings