Simple enough:
- Go into SQL Developer DBA module
- Data Pump -> Create Export Wizard and get to the Summary/Finish page and select the “PL/SQL” tab.
set scan off
set serveroutput on
set escape off
whenever sqlerror exit
DECLARE
h1 number;
errorvarchar varchar2(100):= ‘ERROR’;
tryGetStatus number := 0;
begin
h1 := dbms_datapump.open (operation => ‘EXPORT’, job_mode => ‘TABLE’, job_name => ‘EXPORT_JOB_SQLDEV_1990’, version => ‘COMPATIBLE’);
tryGetStatus := 1;
dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.add_file(handle => h1, filename => ‘EXPDAT.LOG’, directory => ‘DATA_PUMP_DIR’, filetype => 3);
dbms_datapump.set_parameter(handle => h1, name => ‘KEEP_MASTER’, value => 1);
dbms_datapump.metadata_filter(handle => h1, name => ‘SCHEMA_EXPR’, value => ‘IN(”JOBS”)’);
dbms_datapump.metadata_filter(handle => h1, name => ‘NAME_EXPR’, value => ‘IN(”JOBS”,”JOB_UPDATES”)’);
dbms_datapump.add_file(handle => h1, filename => ‘EXPjobs%U.DMP’, directory => ‘DATA_PUMP_DIR’, filesize => ‘100M’, filetype => 1, reusefile => 1);
dbms_datapump.set_parameter(handle => h1, name => ‘INCLUDE_METADATA’, value => 1);
dbms_datapump.set_parameter(handle => h1, name => ‘FLASHBACK_SCN’, value => dbms_flashback.get_system_change_number);
dbms_datapump.set_parameter(handle => h1, name => ‘DATA_ACCESS_METHOD’, value => ‘AUTOMATIC’);
dbms_datapump.set_parameter(handle => h1, name => ‘ESTIMATE’, value => ‘BLOCKS’);
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.detach(handle => h1);
errorvarchar := ‘NO_ERROR’;
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF ((errorvarchar = ‘ERROR’)AND(tryGetStatus=1)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
RAISE;
END;
/