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;

6 comments:

  1. Thanks for this code example.
    I was using this and on execution, getting the following error. Any ideas ? Appreciate your help

    ORA-31626: job does not exist
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 5285
    ORA-06512: at "LPS.DAILY_EXPORT", line 7
    ORA-06512: at line 2



    ReplyDelete
  2. Hi,
    What does line 7 contain in your version of the procedure - is it exactly the same as mine?

    Does your user have explicit create table privilege? (this causes problems if it cant create the master table).

    Does the directory DATA_PUMP_DIR exist , is it accessible to your user?

    Does a full export work as the user you are trying from the command line?

    Cheers,
    Rich

    ReplyDelete
  3. Thanks for the quick response.

    Yes , line 7 is same as yours, (except for FULL, I have put SCHEMA)
    l_dp_handle := DBMS_DATAPUMP.open(operation => 'EXPORT',
    job_mode => 'SCHEMA',
    remote_link => NULL,
    job_name => 'DAILY_EXPORT',

    Yes, it has explicit CREATE TABLE privilege.
    Directory also exists.
    Command line export works..
    There is something tricky in this syntax I think. I have seen this error raised in some forums, but no clear answers.

    ReplyDelete
  4. Please ignore my previous comment,,. looks like there were some special characters as I copied from website. It is working fine now.

    Thanks again.
    George

    ReplyDelete
  5. Hello I have a doubt about the reuse file option. I need to retain 15 days of backups is there any option for that?

    ReplyDelete
  6. Hi,
    the reuse option will just replace the file if it already exists - it's pretty 'dumb'. Ifyou want to retain more than the week that the above example will do you'll need to come up with some different logic in how the files are named - the simplest thing is probably to just choose the day of the month instead - something like this

    select rtrim(to_char(sysdate,'DD'))

    then you get a file for each day of the month - it means you have up to 31 files nd sometimes later days remain for a couple of months until they are overwritten - but it keeps the logic simple.

    Cheers,
    Rich

    ReplyDelete