How to wait for dbms_scheduler jobs to finish

Question

Using Oracle 11.2

Hi,

Here is what I want to do: I'm scheduling jobs using dbms_scheduler. The number of jobs to schedule is not fixed and a max of 4 jobs should run at the same time. The procedure scheduling the jobs should wait until all jobs are completed. If one job fails, the "schedule" procedure should also fail and all remaining scheduled jobs should be deleted from the scheduler.

Currently I have had to sleeping and polling the table user_scheduler_jobs in a loop.

I'm new to PL/SQL and rather inexperienced so please don't be too harsh on me ;)

Here is my code so far.

First the snippet for scheduling the jobs:

BEGIN
  FOR r IN (SELECT p_values FROM some_table WHERE flag = 0 )
  LOOP
    --  count running jobs
    SELECT count(*) INTO v_cnt
    FROM user_scheduler_jobs
    WHERE job_name LIKE 'something%';

    /*
    If max number of parallel jobs is reached, then wait before starting a new one.
    */
    WHILE v_cnt >= l_max_parallel_jobs
    LOOP

      dbms_lock.sleep(10);

      SELECT count(*) INTO v_cnt
      FROM user_scheduler_jobs
      WHERE job_name LIKE 'something%' AND state = 'RUNNING';

      SELECT count(*) INTO v_cnt_failures
      FROM user_scheduler_jobs
      WHERE job_name LIKE 'something%' AND state = 'FAILED' OR state = 'BROKEN';

      IF v_cnt_failures > 0 THEN RAISE some_exception; END IF;

    END LOOP;

    -- Start a new Job
    v_job_name := 'something_someting_' || p_values;
    v_job_action := 'begin user.some_procedure(''' || r.p_values || '''); end;';

    dbms_scheduler.create_job(job_name   => v_job_name,
                              job_type   => 'PLSQL_BLOCK',
                              job_action => v_job_action,
                              comments   => 'Some comment ' || v_job_name,
                              enabled    => FALSE,
                              auto_drop  => FALSE);

    dbms_scheduler.set_attribute(NAME => v_job_name,
                                 ATTRIBUTE => 'max_failures',
                                 VALUE => '1');

    dbms_scheduler.set_attribute(NAME => v_job_name,
                                 ATTRIBUTE => 'max_runs',
                                 VALUE => '1');

    dbms_scheduler.enable(v_job_name);

    v_job_count := v_job_count + 1;

    -- array for all jobs
    v_jobs_aat(v_job_count) := v_job_name;

  END LOOP;

  -- ... Wait till all jobs have finisched. 

  check_queue_completion(v_jobs_aat); -- see procedure below
END;

Procedure for waiting till last four jobs have finisched:

PROCEDURE check_queue_completion(p_jobs_aat IN OUT t_jobs_aat) AS
  v_state user_scheduler_jobs.state%TYPE;
  v_index PLS_INTEGER;
  v_done BOOLEAN := TRUE;

  -- Exceptions
  e_job_failure EXCEPTION;
BEGIN

  WHILE v_done
  LOOP

    v_done := FALSE;

    FOR i IN p_jobs_aat.first..p_jobs_aat.last
    LOOP

      SELECT state INTO v_state FROM user_scheduler_jobs WHERE job_name = p_jobs_aat(i);

      --dbms_output.put_line('Status: ' || v_state);

      CASE

        WHEN v_state = 'SUCCEEDED' OR v_state = 'COMPLETED' THEN
        dbms_output.put_line(p_jobs_aat(i) || ' SUCCEEDED');
        dbms_scheduler.drop_job(job_name => p_jobs_aat(i), force => TRUE);
        p_jobs_aat.delete(i);

        WHEN v_state = 'FAILED' OR v_state = 'BROKEN' THEN
        --Exception auslösen
        dbms_output.put_line(p_jobs_aat(i) || ' FAILED');
        RAISE e_job_failure;

        WHEN v_state = 'RUNNING' OR v_state = 'RETRY SCHEDULED' THEN
        NULL;
        dbms_output.put_line(p_jobs_aat(i) || ' RUNNING or RETRY SCHEDULED');
        v_done := TRUE;

      /*DISABLED, SCHEDULED, REMOTE, CHAIN_STALLED*/
      ELSE
        dbms_output.put_line(p_jobs_aat(i) || ' ELSE');
        dbms_scheduler.drop_job(job_name => p_jobs_aat(i), force => TRUE);
        p_jobs_aat.delete(i);
      END CASE;

    END LOOP;

    hifa.gen_sleep(30);

  END LOOP;

  IF p_jobs_aat.count > 0 THEN delete_jobs_in_queue(p_jobs_aat); END IF;

  EXCEPTION WHEN e_job_failure THEN
  delete_jobs_in_queue(p_jobs_aat);
  RAISE_APPLICATION_ERROR(-20500, 'some error message');

END check_queue_completion;

It does the trick but it seems like some awful hack.

Isn't there a better way to:

Wait until all jobs have finished.
Just run four jobs at a time and start a new one as soon as one of the running jobs has finished.
Throw an exception if one job fails or is broken.

I would leverage the existing features available for dbms_scheduler. For #1, Have you looked at chains (http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CHDCJFCI)? #2 Have you looked at modifying the job_queue_processes? — Patrick Bacon, Feb 16 '15 at 18:02
What Patrick said - check out the scheduler chains mechanism — thatjeffsmith, Feb 16 '15 at 22:48
Thanks for your comments. Unfortunately I can't use job_queue_processes because I'm on a shared DB and they wouldn't let me use "alter system.....". My understanding of job chains is that it allows me to define something like "do first 4 jobs" --> "next 4 jobs" --> "next 4 jobs" and so on but not like "start 4 jobs and as soon as one is finished start the next one". My goal is that at any given time four jobs are running. I also thought about using DBMS_ALERT for waiting on Jobs that would at least reduce the coding overhead a bit. — jon sumisu, Feb 17 '15 at 09:53

score 2 · Answer 1 · answered Feb 25 '16 at 09:48

DECLARE
  cnt NUMBER:=1;
BEGIN
  WHILE cnt>=1
  LOOP
    SELECT count(1) INTO cnt FROM dba_scheduler_running_jobs srj
    WHERE srj.job_name IN ('TEST_JOB1','TEST_JOB2');
    IF cnt>0 THEN
      dbms_lock.sleep (5);
    END IF;
  END LOOP;
  dbms_output.put_line('ASASA');  
END;

score 1 · Answer 2 · answered Jun 21 '15 at 08:27

1

Use dbms_alert or dbms_pipe to send/receive information about job start/finish. Query the jobs table only if you do not receive the information in expected time.

answered Jun 21 '15 at 08:27

Pavel Gatnar

3,987
2
19
29

This pipe or alert only works if sender and receiver are in the same instance. AQ would be a better choice, making it work in RAC. – Dec 23 '15 at 09:35

score 0 · Answer 3 · answered Dec 23 '15 at 09:34

Oracle Scheduler uses Oracle Rersource Manager heavily, Just submit your jobs, defined with an end notification and have a task waiting for your event Q that counts the jobs that are submitted and the jobs that are finished.

You use Oracle Resource manager to control the maximum number of jobs to run concurrently. This will also be based on the total database load, protecting other users agains a system flooded by jobs.

How to wait for dbms_scheduler jobs to finish

3 Answers3