4

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
MP_SteveO
  • 41
  • 1
  • I'm assuming that the jobs aren't idempotent? Can you make them idempotent? – Ben Feb 24 '21 at 21:28
  • I had to lookup that term. Not sure this concept applies. I'm simply trying to form a single file line and make sure nobody cuts. :) Thanks though. – MP_SteveO Feb 25 '21 at 23:52

1 Answers1

0

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.

Vedvyas Mohapatra
  • 146
  • 4
  • A chain will ensure that _different_ steps are run in the correct order. Not that identical jobs that are queued, becauae there's already an instance of that job running, are subsequently run in the order in which they were queued. – Ben Feb 24 '21 at 21:26
  • I believe you are technically correct and I had looked down that route. But jobs are never re-run, just 1 and done, don't know when or if a second is coming. So I would have to come up with something convoluted to dynamically see existing and create new steps, delete them, etc. Seems like FIFO **should** come for free. Easier to roll my own with a queue table and a simple date and some polling. I was surprised when priority did not do the trick. Thank you and I see some great applications for chains. For now I'm just letting late stragglers jump the line (maybe) :) – MP_SteveO Feb 26 '21 at 00:03