Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Database Tips by Donald Burleson


The Oracle supplied dbms_datapump package provides a PL/SQL API to the exact same functionality as the prior two sections about the IMPDP and EXPDP command line utilities. But the reason is not the expectation that DBAs will flock to use this programmatic interface, but rather to provide a well documented and standardized API for OEM and other third party tools to call for those users who prefer using screens.


The dbms_datapump package also supports the flashback_time argument, Flashback_time is set with the dbms_datapump set_parameter argument.


Also see these notes on dbms_datapump:

As such, this method will not be documented to the same level of detail, but rather just highlight the PL/SQL package specification reference information and a single example. Here are programmatic interfaces available from this Oracle supplied PL/SQL package followed by the simple export schema example using this API.


-- Add the file to the data pump import/export file set


PROCEDURE add_file (

    handle       IN  NUMBER,

    filename     IN  VARCHAR2,

    directory    IN  VARCHAR2 DEFAULT NULL,

    filesize     IN  VARCHAR2 DEFAULT NULL,


    reusefile    IN  NUMBER DEFAULT NULL



-- Attach the current database session to a data pump job process


FUNCTION attach (

    job_name     IN  VARCHAR2 DEFAULT NULL,

    job_owner    IN  VARCHAR2 DEFAULT NULL




-- Permits the specification of data pump job table data filtering


PROCEDURE data_filter (

    handle        IN  NUMBER,

    name          IN  VARCHAR2,

    value         IN  NUMBER | CLOB | VARCHAR2,

    table_name    IN  VARCHAR2 DEFAULT NULL,

    schema_name   IN  VARCHAR2 DEFAULT NULL



-- Detach the current database session to a data pump job process


PROCEDURE detach (

    handle    IN  NUMBER



-- Returns the extended data pump file information


PROCEDURE get_dumpfile_info (

    filename      IN     VARCHAR2,

    directory     IN     VARCHAR2,

    info_table    OUT    ku$_dumpfile_info,

    filetype      OUT    NUMBER



-- Returns the status of a data pump job


FUNCTION get_status (

    handle     IN  NUMBER,

    mask       IN  INTEGER,

    timeout    IN  NUMBER DEFAULT NULL


  RETURN ku$_Status;


PROCEDURE get_status (

    handle        IN  NUMBER,

    mask          IN  INTEGER,

    timeout       IN  NUMBER DEFAULT NULL,

    job_state     OUT VARCHAR2,

    status        OUT ku$_Status1010 | ku$_Status1020



-- Permits the insertion of a message into the log file


PROCEDURE log_entry (

    handle          IN  NUMBER,

    message         IN  VARCHAR2,

    log_file_only   IN  NUMBER DEFAULT 0



-- Permits the specification of data pump job metadata filtering


PROCEDURE metadata_filter (

    handle         IN  NUMBER,

    name           IN  VARCHAR2,

    value          IN  VARCHAR2 | CLOB,

    object_path    IN  VARCHAR2 DEFAULT NULL,

    object_type    IN  VARCHAR2 DEFAULT NULL


-- Permits the specification of data pump job object re-mappings


PROCEDURE metadata_remap (

    handle         IN  NUMBER,

    name           IN  VARCHAR2,

    old_value      IN  VARCHAR2,

    value          IN  VARCHAR2,

    object_type    IN  VARCHAR2 DEFAULT NULL



-- Permits the specification of data pump job metadata transformations


PROCEDURE metadata_transform (

    handle         IN  NUMBER,

    name           IN  VARCHAR2,

    value          IN  VARCHAR2 | NUMBER,

    object_type    IN  VARCHAR2 DEFAULT NULL



-- Declares a new data pump job returning handle required for other API calls



    operation      IN  VARCHAR2,

    job_mode       IN  VARCHAR2,

    remote_link    IN  VARCHAR2 DEFAULT NULL,

    job_name       IN  VARCHAR2 DEFAULT NULL,

    version        IN  VARCHAR2 DEFAULT 'COMPATIBLE',





-- Permits the specification of data pump job parallelization degree


PROCEDURE set_parallel (

    handle     IN  NUMBER,

    degree     IN  NUMBER



-- Permits the specification of data pump job-processing options


PROCEDURE set_parameter (

    handle     IN  NUMBER,

    name       IN  VARCHAR2,

    value      IN  NUMBER | VARCHAR2



-- Begin or resume the execution of a data pump job


PROCEDURE start_job (

    handle          IN  NUMBER,

    skip_current    IN  NUMBER DEFAULT 0,

    abort_step      IN  NUMBER DEFAULT 0,

    cluster_ok      IN  NUMBER DEFAULT 1,

    service_name    IN  VARCHAR2 DEFAULT NULL



-- Terminate the execution of a data pump job


PROCEDURE stop_job (

    handle         IN  NUMBER,

    immediate      IN  NUMBER DEFAULT 0,

    keep_master    IN  NUMBER DEFAULT NULL,

    delay          IN  NUMBER DEFAULT 60



-- Runs a data pump job until it completes or aborts


PROCEDURE wait_for_job (

    handle        IN  NUMBER,

    job_state     OUT VARCHAR2



Here is the command line version of the schema export once again that is the slightly more involved version utilizing PL/SQL API coding as the interface.


C:\> expdp bert/bert directory=data_pump_dir dumpfile=multi_schema.dmp schemas=bert,movies



  handle NUMBER;

  status VARCHAR2(20);



  DBMS_DATAPUMP.ADD_FILE (handle, 'multi_schema.dmp', 'DATA_PUMP_DIR');



  DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);



Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.