Job looping in Oracle scheduler

Question

In oracle 11g, if I have a job A, is it possible to schedule it in such a way that, the job will repeatedly execute from 10am to 11am every day.

Answer 1

Yes it can be scheduled.

Oracle Job Queue

The Oracle job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals. Oracle provides a built-in package DBMS_JOB to schedule the jobs. The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue. The DBMS_JOB package is created when the Oracle database is installed.

You can get details on : https://www.developer.com/db/article.php/3713896/Scheduling-Jobs-in-the-Database.htm

Answer 2

You can do as follows, the below will execute from 10 A.M. to 11 A.M. repeatedly every 10 minutes (change the frequency as per your requirement)

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'JOB_NAME',
      job_type          => 'PLSQL_BLOCK',
      job_action        => 'BEGIN your_procedure_name; END;',
      start_date        => SYSDATE,
      repeat_interval   => 'FREQ=DAILY; BYHOUR=10,11; BYMINUTE=0,10;',
      enabled           => TRUE,
      auto_drop         => FALSE);
END;
/

"it should execute as much time as possible"

Keep in mind, the job you are executing as much as possible during the one hour time should not be overkill

Answer 3

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name          => 'JOB_NAME',
    job_type          => 'PLSQL_BLOCK',
    job_action        => 'BEGIN your_procedure_name; END;',
    start_date        => SYSTIMESTAMP,
    repeat_interval   => 'FREQ=SECONDLY;BYHOUR=10,11;',
    enabled           => TRUE,
    auto_drop         => FALSE);
END;
/

This will execute the job action at most once a second, depending on the duration of the job. One second resolution between successive job executions is a dbms_scheduler limitation. The repeat_interval in case of an ical expression is executed after the job finished, so if a duration is 10 seconds then it will execute after at most once every 10 seconds. There can only be a single instance of the same job executing at the same time.

If you need to execute an action multiple times a second then you will need to create multiple jobs with the same action and secondly interval, which then could execute at the same time.

If you still need multiple executions per second but only one instance should be running at all times (that will run within a fraction of a second), then attach the same resource to each job.

begin 

 dbms_scheduler.create_resource(resource_name=>'TEST_RESOURCE',
            units=>'1'); 

DBMS_SCHEDULER.set_resource_constraint (
               object_name   => 'JOB1_TEST_RESOURCE',
               resource_name => 'TEST_RESOURCE',
               units         => 1); 

DBMS_SCHEDULER.set_resource_constraint (
               object_name   => 'JOB2_TEST_RESOURCE',
               resource_name => 'TEST_RESOURCE',
               units         => 1); 
END;
/

One last thing is that if using systimestamp as the start date the byhour values are fixed as in the current gmt offset. For example if the job was created when daylight saving time was active, it could behave as specified BYHOUR=9,10 in standard time(winter time). This depends on the timezone value of your operating system the database is running on. To avoid this use "at time zone" as in

SYSTIMESTAMP AT TIME ZONE 'US/Pacific'.

Also one could omit the start_time provided that the scheduler default time zone is set correctly. See

dbms_scheduler.get_scheduler_attribute('default_timezone')

and

dbms_scheduler.set_scheduler_attribute('default_timezone', 'your time zone')