Oracle Job Scheduler Archivelog Deletion

CREATE OR REPLACE PROCEDURE archive_dir_setup AS

archive_dir VARCHAR2(40);

BEGIN

EXECUTE IMMEDIATE

'SELECT DESTINATION '||

' FROM v$archive_dest '||

' WHERE dest_name = ''LOG_ARCHIVE_DEST_1'''

INTO archive_dir;

EXECUTE IMMEDIATE

'CREATE DIRECTORY ARCHIVE_DIR '||

' AS '''||archive_dir||'''';

END archive_dir_setup;

/

EXEC archive_dir_setup;

CREATE OR REPLACE PROCEDURE archive_del as

BEGIN

FOR cur_rec IN (select name from v$archived_log where trunc(first_time)< trim(sysdate-7) ORDER BY first_time) LOOP

BEGIN

DBMS_OUTPUT.PUT_LINE ('begin utl_file.fremove(''ARCHIVE_DIR'','''||cur_rec.name||''') end;');

utl_file.fremove('ARCHIVE_DIR',''|| cur_rec.name ||'');

END;

END LOOP;

END archive_del;

/

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'archive_deletion',

job_type => 'STORED_PROCEDURE',

job_action => 'archive_del',

start_date => '01-APR-12 02:00.00.00 AM ASIA/CALCUTTA',

repeat_interval => 'freq=DAILY',

end_date => NULL,

enabled => TRUE,

comments => 'Delete 7 days old Archive logs');

END;

/