Oracle DBMS_SCHEDULER use case to run 1 job at a time but queue subsequent jobs to run FIFO

user_15214854 0
Viewed 282

Create a resource, and limit jobs to 1

begin dbms_scheduler.create_resource(resource_name=>'SO_TEST_RESOURCE',units=>'1'); END;

While I can create a job, assign a resource, and even a priority, the subsequent jobs (assigned to the same resource and various priorities) that are queued, are run in random order not FIFO, and not in priority order. Looking for a way to force the next job queued (assigned to that same resource) to be the one that runs next.

DBMS_SCHEDULER.create_job (
                       job_name        => 'SO_JOB1_TEST_RESOURCE',
                       job_type        => 'PLSQL_BLOCK',
                       job_action      => 'begin DBMS_SESSION.sleep(40); end;',
                       auto_drop       => true,
                       start_date      => systimestamp,
                       enabled         => false);

 DBMS_SCHEDULER.set_resource_constraint (
                   object_name   => 'SO_JOB1_TEST_RESOURCE',
                   resource_name => 'SO_TEST_RESOURCE',
                   units         => 1); 

  DBMS_SCHEDULER.SET_ATTRIBUTE(
  NAME                                        => 'SO_JOB1_TEST_RESOURCE',
  ATTRIBUTE                                   => 'job_priority',
  VALUE                                       =>1 );                   
                   
   DBMS_SCHEDULER.enable('SO_JOB1_TEST_RESOURCE'); 

.... adding more jobs 2, 3, 4 run in random order
dbms-scheduler oracle
1 Answers

Oracle DBMS_SCHEUDLER CHAINS is probably what you are looking for. You can create a chain first

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => NULL,
   comments            => 'My first chain');
END;
/

... and then add each scheduled job into the chain as steps in the chain.

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step1',
   program_name    =>  'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step2',
   program_name    =>  'my_chain2');
END;
/

There is a lot more that can be done with job CHAINS, like checking status, implementing restart logic etc. Oracle Documentation will be good reference.

user_127843970
Related