Sunday, January 3, 2016

How to remove and add or resubmit a Job to oracle scheduler using DBMS_JOB


How to remove and add or resubmit a Job to oracle scheduler using DBMS_JOB

Get the JOB ID or NUMBER
Select job from user_jobs where what like '%testjob_jobs.schedule_jobs%';

1342086

Remove the job from the scheduler:

exec DBMS_JOB.REMOVE(1342086);
or 
BEGIN 
  SYS.DBMS_JOB.REMOVE(984909);
COMMIT;
END;
/



Add or resubmit the job to the scheduler:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'declare out_list varchar2(32767); begin testjob_jobs.schedule_jobs(job,out_list,''N'',''N''); end;'
   ,next_date => to_date('10/01/2012 07:00:46','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'sysdate + 5/(24*60)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

No comments: