0

I want to create oracle dbms scheduler job, with these atributes:

FREQ=MINUTELY INTERVAL=10 BYDAY=MON TUE WED THU FRI 
BYHOUR=7,8,9,10,11,12,13,14,15,16,17,18

But, beside that, I want in period from 14 to 16pm that this same job works on every 5 minutes, and before 14pm and after 16pm to work on 10minutes.

Moudiz
  • 7,211
  • 22
  • 78
  • 156
Air Bnb
  • 1
  • Please read this http://www.dba-oracle.com/t_linux_oracle_dbms_scheduler_create_job.htm . It will give you an insight of how to create a job. – XING Oct 20 '17 at 08:27
  • should I create new job wich will work only from 14 to 16 pm in 5 minutes, so I would have 2 jobs with same procedure – Air Bnb Oct 20 '17 at 08:29
  • @AirBnb you may kindly refer to https://stackoverflow.com/questions/46617559/how-to-schedule-oracle-dbms-jobs-in-a-window/46618695#46618695 – Barbaros Özhan Oct 20 '17 at 09:05

1 Answers1

2

The best way would be to create schedules:

BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'my_first_schedule',
  start_date        => SYSTIMESTAMP,
  repeat_interval   => 'FREQ=MINUTELY;INTERVAL=5;BYHOUR=14,15,16;BYDAY=MON,TUE,WED,THU,FRI',
  comments          => 'Every 10 minutes from 2pm to 4pm');
END;
/

BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'my_second_schedule',
  start_date        => SYSTIMESTAMP,
  repeat_interval   => 'FREQ=MINUTELY;INTERVAL=10;BYHOUR=7,8,9,10,11,12,13,17,18;BYDAY=MON,TUE,WED,THU,FRI',
  comments          => 'Every 5 minutes from 7am to 2pm and from 5pm to 6pm');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
     JOB_NAME           =>  'JOB',
     JOB_TYPE           =>  'STORED_PROCEDURE',
     JOB_ACTION         =>  'SCHED_TEST_JOB_PROCEDURE',
     START_DATE         =>  SYSTIMESTAMP,
     REPEAT_INTERVAL    =>  'FREQ=my_first_schedule,my_second_schedule;',
     AUTO_DROP          =>  FALSE,
     ENABLED            =>  TRUE);
END;
/
Cyrille MODIANO
  • 2,246
  • 2
  • 21
  • 33
  • Why should I use 3rd job? – Air Bnb Oct 20 '17 at 09:03
  • there are 2 schedules and 1 job. the job uses the 2 schedules – Cyrille MODIANO Oct 20 '17 at 09:04
  • Whether this third job is necessary..? – Air Bnb Oct 20 '17 at 09:24
  • there is only 1 job, the first 2 are schedules. You need to create the schedules and then use them when you create your job – Cyrille MODIANO Oct 20 '17 at 09:28
  • this is an absolutely great approach, i didnot know about that – Thomas Oct 20 '17 at 10:22
  • Thanks! I asked you why shoukld i create 2 sch.jobs and one job? If I create the 2 sch.jobs to start in specific time, why should than I create thes simple job with START_DATE => SYSTIMESTAMP, REPEAT_INTERVAL => 'FREQ=my_first_schedule,my_second_schedule;', – Air Bnb Oct 20 '17 at 10:40
  • You don’t understand. A schedule is just a repeating task, it doesn’t start anything. Creating only the schedules is meaningless. The job will start a procedure or a plsql block at a frequency specified in the repeat interval. – Cyrille MODIANO Oct 20 '17 at 14:12