Managing QUEUED_TASKS

Delete 'COMPLETED' and 'ERROR' QUEUED_TASKS every SUNDAY at 4.30am

DECLARE

    JobNo dba_jobs.job%TYPE;

BEGIN

  --schedule cleanup of QUEUED_TASKS each Sunday morning at 4:30am:

  DBMS_JOB.SUBMIT(JobNo, 'DELETE FROM queued_tasks WHERE status IN (''C'',''E'') AND end_date < sysdate - 7;',

    trunc(sysdate) + 4/24 + 1/48 + 8 - to_char(sysdate,'D'),

   'trunc(sysdate) + 4/24 + 1/48 + 7');

END;

/