DBMS_DATAPUMP daily full export

A simple procedure to do a daily full export of your database ( works on a weekly cycle - so one file per day of the week)



Note: the user that owns the procedure must have explict 'CREATE TABLE' privileges or this won't work - even if your user has DBA it won't work....

CREATE OR REPLACE PROCEDURE DAILY_EXPORT AS

  l_dp_handle      NUMBER;

  v_job_state      varchar2(4000);

  v_day            varchar2(20);

BEGIN

  select rtrim(to_char(sysdate,'DAY')) into v_day from dual;

  l_dp_handle := DBMS_DATAPUMP.open(operation   => 'EXPORT',

                                    job_mode    => 'FULL',

                                    remote_link => NULL,

                                    version     => 'LATEST');

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,

                         filename  => v_day||'.dmp',

                         directory => 'DATA_PUMP_DIR',

                         reusefile => 1);

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,

                         filename  => v_day||'.log',

                         directory => 'DATA_PUMP_DIR',

                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,

                         reusefile => 1);

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);

  DBMS_OUTPUT.PUT_LINE(v_job_state);

END;

  Then run the extract (or schedule via your favourite scheduler.....)

begin

  DAILY_EXPORT;

end;