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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

DBMS_DATAPUMP Tips

Oracle Database Tips by Donald Burleson

DBMS_DATAPUMP

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,

    filetype     IN  NUMBER DEFAULT KU$_FILE_TYPE_DUMP_FILE,

    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

  )

  RETURN NUMBER;

 

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

 

FUNCTION open (

    operation      IN  VARCHAR2,

    job_mode       IN  VARCHAR2,

    remote_link    IN  VARCHAR2 DEFAULT NULL,

    job_name       IN  VARCHAR2 DEFAULT NULL,

    version        IN  VARCHAR2 DEFAULT 'COMPATIBLE',

    compression    IN  NUMBER DEFAULT KU$_COMPRESS_METADATA

  )

  RETURN NUMBER;

 

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

 

DECLARE

  handle NUMBER;

  status VARCHAR2(20);

BEGIN

  handle := DBMS_DATAPUMP.OPEN ('EXPORT', 'SCHEMA');

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

  DBMS_DATAPUMP.METADATA_FILTER (handle, 'SCHEMA_EXPR', 'IN (''BERT'',''MOVIES'')');

  DBMS_DATAPUMP.START_JOB (handle);

  DBMS_DATAPUMP.WAIT_FOR_JOB (handle, status);

END;

/

 
 
 
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.