Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

DBMS_DATAPUMP - Import
SET SERVEROUTPUT ON;

DECLARE
  v_dp_handle      NUMBER;
  v_job_state      VARCHAR2(4000);

BEGIN
  v_dp_handle := DBMS_DATAPUMP.open(
                   operation => 'IMPORT',
                   mode      => 'FULL',
                   job_name  => Null
                 );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_01.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_02.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_03.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_04.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.start_job(
       handle => v_dp_handle
     );

  DBMS_DATAPUMP.wait_for_job(
       handle    => v_dp_handle,
       job_state => v_job_state
     );
 
  DBMS_OUTPUT.put_line('Job State '||v_job_state);
END;
/

-- To Import a few tables from specific schema

SET SERVEROUTPUT ON;

DECLARE
  v_dp_handle      NUMBER;
  v_job_state      VARCHAR2(4000);

BEGIN
  v_dp_handle := DBMS_DATAPUMP.open(
                   operation => 'IMPORT',
                   mode      => 'FULL',
                   job_name  => Null
                 );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_01.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_02.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_03.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.add_file(
    handle    => v_dp_handle,
    filename  => 'EXPORT_DBUSER_110001_04.dmp',
    directory => 'DATA_PUMP_DIR'
   );

  DBMS_DATAPUMP.meta_filter(
            handle => v_dp_handle,
            name   => 'SCHEMA_LIST',
            value  => q'|'EDW','ODS'|'
          );

  DBMS_DATAPUMP.meta_filter(
            handle      => v_dp_handle,
            name        => 'NAME_EXPR',
            value       => q'|IN ('EDW_FCT_SALES','ODS_ALL_TRANS')|',
            object_path => 'TABLE'
          );

-- Value can be any one of these APPEND, REPLACE, SKIP, TRUNCATE
-- as required by application

  DBMS_DATAPUMP.set_parameter(
            handle => v_dp_handle,
            name   => 'TABLE_EXISTS_ACTION',
            value  => 'APPEND'
          );

  DBMS_DATAPUMP.start_job(
       handle => v_dp_handle
     );

  DBMS_DATAPUMP.wait_for_job(
       handle    => v_dp_handle,
       job_state => v_job_state
     );
 
  DBMS_OUTPUT.put_line('Job State '||v_job_state);
END;
/


Data Pump in Oracle Database 11g Release 2: Foundation for Ultra High-Speed Data Movement Utilities

Oracle Database Online Documentation 11g Release 1: DBMS_DataPump

DBMS_DATAPUMP Import Status

DBMS_DATAPUMP Export

Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 19th, 2014

  39912