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

Beliebte Postings