Datapump Export Example Using FLASHBACK_TIME

Use this as a parameter value when creating nightly export job in OEM.  This uses SYSDATE for the FLASHBACK_TIME.  It also appends the current date to the dump and log file names.

SET SERVEROUTPUT ON
variable exit_code NUMBER
DECLARE
  h1   NUMBER;
  js VARCHAR2(20);
  dt VARCHAR2(12);
begin
:exit_code := 0;
    select to_char(sysdate,'YYYYMONDD') into dt from dual;
    h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'FULL', job_name => 'PRODDB_EXPORT', version => 'COMPATIBLE');
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle => h1, filename => 'PRODDBEXP' ||dt||'.LOG', directory => 'PRODDB_EXP_DIR', filetype => 3);
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
    dbms_datapump.add_file(handle => h1, filename => 'PRODDBEXP'||dt||'.DMP', directory => 'PRODDB_EXP_DIR', filetype => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value => 'TO_TIMESTAMP (to_char(sysdate,''dd.mm.yyyy HH24:MI:SS''),''dd.mm.yyyy HH24:MI:SS'')');
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
    dbms_datapump.detach(handle => h1);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(TO_CHAR(SQLCODE)||' :'||SQLERRM);
:exit_code := 2;
  ROLLBACK;
end;
/
exit :exit_code;