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;

Preview dump file before importing

If using datapump, the option is SQLFILE.

The file_name specifies where the import job will write the DDL that would be
executed during the job. The SQL is not actually executed, and the target system
remains unchanged. The file is written to the directory object specified in the
DIRECTORY parameter, unless another directory_object is explicitly specified
here. Any existing file that has a name matching the one specified with this parameter
is overwritten.

Example:
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp SQLFILE=dpump_dir2:filename

If using original import the option is SHOW=Y.

The SHOW parameter can be used only with the FULL=y, FROMUSER, TOUSER, or
TABLES parameter.