Data Pump PL/SQL partial export

June 10, 2008  |  dba, pl/sql

Since the release 10g, Oracle have redesigned the export / import method. Oracle still ships EXP and IMP but DATA PUMP is now the standard tool for fast data movement between databases. DATA PUMP syntax is very similar to EXP/IMP, although its technology is entirely different.

Data Pump runs independent database jobs (different from traditionally client EXP/IMP) in a distributed environment.  For instance, the DATA PUMP scheduler user can be different from the one that is monitoring the status of that export / import job.  The exported data is saved /loaded in / from a database DIRECTORY object and the user that exports must have write /read access to it.

What I’ll write today is not about DATA PUMP itself and all it features (google it it’s easy), but is to describe a simple way use the DBMS_DATAPUMP PL/SQL API to to export partial data from some tables on my database.  I guess the code explains itself…

First, create a database directory:

CREATE OR REPLACE DIRECTORY MY_DIR AS '/opt/my_oracle_dir';
GRANT READ, WRITE ON DIRECTORY MY_DIR TO joni;

Next, let’s create the procedure:

CREATE OR REPLACE PROCEDURE partial_export
AS
l_dp_handle        NUMBER;
l_last_job_state   VARCHAR2 (30)  := 'UNDEFINED';
l_job_state        VARCHAR2 (30)  := 'UNDEFINED';
l_sts              ku$_status;
l_job_name         VARCHAR2 (100);
l_dirname          VARCHAR2 (100);
l_filename         VARCHAR2 (100);
BEGIN
l_filename := 'myexpfile.dmp';
-- sets the job name
l_job_name := 'BZ' || SYSDATE;
l_dp_handle :=
DBMS_DATAPUMP.OPEN (operation        => 'EXPORT',
job_mode         => 'TABLE',
remote_link      => NULL,
job_name         => l_job_name,
VERSION          => 'LATEST'
);
--specify the database directory  and the filename for the export file
DBMS_DATAPUMP.add_file (handle         => l_dp_handle,
filename       => l_filename,
DIRECTORY      => 'MY_DYR'
);
DBMS_DATAPUMP.add_file (handle         => l_dp_handle,
filename       => l_filename || '.LOG',
DIRECTORY      => 'MY_DYR',
filetype       => DBMS_DATAPUMP.ku$_file_type_log_file
);
--specify the tables that I want to export. (CLIENTES,FORNECEDORES)
DBMS_DATAPUMP.metadata_filter (handle      => l_dp_handle,
NAME        => 'NAME_EXPR',
VALUE       => 'IN (''CLIENTES'', ''FORNECEDORES'')'
);
-- set subset data export. exports only rows that id_empresa equals 2050
DBMS_DATAPUMP.data_filter (handle          => l_dp_handle,
NAME            => 'SUBQUERY',
VALUE           => 'WHERE ID_EMPRESA=2050',
table_name      => 'CLIENTES'
);
-- set subset data export. exports only rows that id_empresa equals 2050
DBMS_DATAPUMP.data_filter (handle          => l_dp_handle,
NAME            => 'SUBQUERY',
VALUE           => 'WHERE ID_EMPRESA=2050',
table_name      => 'FORNECEDORES'
);
DBMS_DATAPUMP.start_job (l_dp_handle);
DBMS_DATAPUMP.detach (l_dp_handle);
END;

It’s ready to be invoked! Try it!!! Now you can export DATA directly from your application without any special coding. Just call this procedure! Next, you just have to code the method to get all that data out of the server directory 🙂


11 Comments


  1. data_filter needs the schema_name parameter. Without it I get an ORA-39001. With it works fine.

  2. Hi “Nobody”:

    In my 10g release it works without schema_name in data_filter!!

    Regards,
    Joao Oliveira

  3. how about Data Pump PL/SQL Schema export

  4. Code generates these errors…..

    ERROR at line 1:
    ORA-39001: invalid argument value
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2926
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3162
    ORA-06512: at “HR.PARTIAL_EXPORT”, line 22
    ORA-06512: at line 1

  5. Same error as of IMRAN KHAN

  6. hi,

    did you used the name_expr metadata filter i used? or the table_name?

    Have you added schema_name?

    i used:
    DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle,
    NAME => ‘NAME_EXPR’,
    VALUE => ‘IN (”CLIENTES”, ”FORNECEDORES”)’
    );

    you can also do it like this:
    DBMS_DATAPUMP.metadata_filter (handle => l_dp_handle, name => ‘SCHEMA_EXPR’, value => ‘IN (“MYSCHEMA”)’);

    Say something… waiting your answer if this hint helped.

    Regards
    Joao

  7. MY error was.

    Ensure that you have the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. To see a list of all roles assigned to you within your security domain, do the following:

    SQL> SELECT * FROM SESSION_ROLES;

  8. I also got one basic question, I want to export schema with table also. Is it possible?

  9. Getting following error while executing the procedure:

    ERROR at line 1:
    ORA-31634: job already exists
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 911
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4354
    ORA-06512: at “SHUBHO.PARTIAL_EXPORT”, line 14
    ORA-06512: at line 1

    Elapsed: 00:00:00.03
    SHUBHO@XE >
    SHUBHO@XE >
    SHUBHO@XE > SELECT * FROM SESSION_ROLES;

    ROLE
    ——————————
    CONNECT
    RESOURCE
    DBA
    SELECT_CATALOG_ROLE
    HS_ADMIN_ROLE
    EXECUTE_CATALOG_ROLE
    DELETE_CATALOG_ROLE
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE
    GATHER_SYSTEM_STATISTICS
    SCHEDULER_ADMIN
    PLUSTRACE
    XDBADMIN
    XDBWEBSERVICES

    14 rows selected.

  10. tried with different job name and then following error mesage:
    SHUBHO@XE > exec partial_export;
    BEGIN partial_export; END;

    *
    ERROR at line 1:
    ORA-39001: invalid argument value
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2926
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3162
    ORA-06512: at “SHUBHO.PARTIAL_EXPORT”, line 23
    ORA-06512: at line 1

    Elapsed: 00:00:00.48
    SHUBHO@XE >

    Please assist…

  11. I recently got to this unique site a few weeks ago.
    I was really captured with the little bit of resources you have got here.
    Huge browses upwards in making such fantastic wewebsite
    page!

Trackbacks

  1. Data Pump PL/SQL error when called via procedure « Beyond Oracle
  2. ORA-01017 running EXPDP from Enterprise Manager | Beyond Oracle

Leave a Reply