EXPDP Data Pump Job Scheduling with rename dump and remove old files

1) create directory export_auto as 'd:\expdp1213';

create user dba_export_user identified by test123;

grant connect, create database link, resource, create view to dba_export_user;

grant unlimited tablespace to dba_export_user;

grant exp_full_database to dba_export_user;

grant read,write on directory export_auto to dba_export_user;

grant execute on dbms_flashback to dba_export_user;

grant create table to dba_export_user;

grant FLASHBACK ANY TABLE to dba_export_user;

2)

CREATE OR REPLACE PROCEDURE dba_export_user.start_export

IS

hdl_job NUMBER;

l_cur_scn NUMBER;

l_job_state VARCHAR2 (20);

l_status SYS.ku$_status1010;

l_job_status SYS.ku$_jobstatus1010;

BEGIN

begin

execute immediate 'drop table dba_export_user.AUTO_EXPORT';

exception when others then null;

end;

hdl_job := DBMS_DATAPUMP.OPEN ( operation => 'EXPORT', job_mode => 'FULL', job_name => 'AUTO_EXPORT' );

DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'exp1213.dmp',directory => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);

DBMS_DATAPUMP.add_file (handle => hdl_job,filename => 'export.log',DIRECTORY => 'EXPORT_AUTO',filetype => DBMS_DATAPUMP.ku$_file_type_log_file);

DBMS_DATAPUMP.start_job (handle => hdl_job);

DBMS_DATAPUMP.wait_for_job (handle => hdl_job, job_state => l_job_state);

DBMS_OUTPUT.put_line ('Job exited with status:' || l_job_state);

DBMS_DATAPUMP.detach(handle => hdl_job);

----------------------RENAME BACKUP WITH DATE

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','exp1213.DMP','EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FRENAME ('EXPORT_AUTO','export.log','EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE,'DDMMYYYY')||'.LOG');

end;

------------DELETE TWO DAYS BEFORE BACKUP

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','exp1213'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.DMP');

end;

begin

UTL_FILE.FREMOVE ('EXPORT_AUTO','export'||'_'||TO_CHAR(SYSDATE-2,'DDMMYYYY')||'.log');

end;

END;

/

3) Change the time, Date

begin

dbms_scheduler.create_job(

job_name => 'EXPORT_JOB'

,job_type => 'STORED_PROCEDURE'

,job_action => 'dba_export_user.start_export'

,start_date => '08-FEB-12 06.02.00.00 PM ASIA/CALCUTTA'

,repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;'

,enabled => TRUE

,comments => 'EXPORT_DATABASE_JOB');

end;

/

Note: Rename the dmp file with sysdate on daily basis before next schedule time

manually execute backup job

EXEC dba_export_user.start_export;

check running job status

select * from DBA_datapump_jobs;

drop job

EXEC dbms_scheduler.drop_job('dba_export_user.start_export');