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; /
24057