Oracle scheduler jobs with DBMS_SCHEDULER: program, schedule and job in one re-runnable script

A scheduler job runs PL/SQL for you on a timetable: every 10 seconds, every night at 02:00, on the last day of the month. The quickest way to make one is a single create_job call. For jobs that stay in an application for years, I use three objects and one script that can be run again at any time:

• a program: what runs (a procedure),
• a schedule: when it runs,
• a job: the program and the schedule put together, and the thing you turn on and off.

You can change each part without touching the others. Several jobs can share one schedule. And the script drops and rebuilds everything, so it’s the same on every database you deploy it to.

Every call and every result below was run on Oracle AI Database 26ai (23.26), the Free edition on my own machine. DBMS_SCHEDULER is much older than that release, but I only ran these tests on 26ai.

The example#

A table, and a procedure that processes a queue. Here it just writes one row, which is enough to see when the job really ran:

create table job_demo_log (
  id        number generated always as identity,
  logged_at timestamp default systimestamp,
  note      varchar2(200));

create or replace procedure process_order_queue is
begin
  insert into job_demo_log (note) values ('queue run');
  commit;
end;
/

Your schema needs the CREATE JOB privilege.

The script: program, schedule, job#

begin
  -- 1. drop the old copies, so the script can be re-run: the job first, then what it uses
  for o in (select 1 as seq, 'JOB' as t, job_name as n from user_scheduler_jobs where job_name = 'ORDER_QUEUE_JOB'
            union all
            select 2, 'SCH', schedule_name from user_scheduler_schedules where schedule_name = 'ORDER_QUEUE_SCH'
            union all
            select 3, 'PRG', program_name from user_scheduler_programs where program_name = 'ORDER_QUEUE_PRG'
            order by 1)
  loop
    case o.t
      when 'JOB' then dbms_scheduler.drop_job(o.n, force => true);   -- force: stops it if it is running
      when 'SCH' then dbms_scheduler.drop_schedule(o.n);             -- no force: see below
      when 'PRG' then dbms_scheduler.drop_program(o.n);
    end case;
  end loop;

  -- 2. the program: WHAT runs
  dbms_scheduler.create_program(
    program_name   => 'ORDER_QUEUE_PRG',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'PROCESS_ORDER_QUEUE',
    enabled        => true);

  -- 3. the schedule: WHEN it runs (every 10 seconds)
  dbms_scheduler.create_schedule(
    schedule_name   => 'ORDER_QUEUE_SCH',
    start_date      => systimestamp,
    repeat_interval => 'FREQ=SECONDLY;INTERVAL=10');

  -- 4. the job: program + schedule, created disabled
  dbms_scheduler.create_job(
    job_name      => 'ORDER_QUEUE_JOB',
    program_name  => 'ORDER_QUEUE_PRG',
    schedule_name => 'ORDER_QUEUE_SCH',
    enabled       => false,
    auto_drop     => false);

  -- 5. settings first, then enable
  dbms_scheduler.set_attribute('ORDER_QUEUE_JOB', 'max_failures', 10);
  dbms_scheduler.enable('ORDER_QUEUE_JOB');
end;
/

I ran it twice in a row. The second run dropped and rebuilt everything without an error, and the job then ran every 10 seconds.

Why the drops are written this way#

Order: job first. A program or schedule that a job still uses can’t be dropped:

ORA-27479: Cannot drop "AILAB"."ORDER_QUEUE_SCH" because other objects depend on it

So the job goes first, and after that nothing depends on the other two. Write the order out (seq 1, 2, 3). My first version of this loop sorted on the type names, order by 1 desc. That puts 'SCH' before 'PRG' before 'JOB', which is the opposite of what its comment said. It only worked because every drop had force => true.

force only on the job. drop_schedule(..., force => true) does drop a schedule that jobs still use. I tried it with two jobs on one schedule: the drop worked, and both jobs became DISABLED. That’s fine for your own job, which the script drops anyway. It isn’t fine if someone else’s job shares the schedule, because that job silently stops running. Without force, the script stops with ORA-27479 instead and tells you.

One thing to know when that happens: by then your own job has already been dropped. Scheduler calls take effect immediately and aren’t rolled back when the block fails. Fix the cause and run the script again.

Why the job is created disabled#

enabled => false, then set_attribute, then enable: the job can’t start a run before all its settings are in place. Two other defaults to know if you create jobs differently:

• enabled defaults to FALSE. A job created without it raises no error and never runs.
• auto_drop defaults to TRUE. A job that runs only once (no schedule, no repeat_interval) is dropped after it runs, even if the run fails. I created one, and five seconds later it was gone, with only its row in the run history left. That’s why the script says auto_drop => false.

See the job#

The job, its program and its schedule are three rows in three views. Join them to see everything in one row:

select j.job_name, j.enabled, j.state,
       j.program_name, p.program_action,
       j.schedule_name, s.repeat_interval,
       j.next_run_date, j.run_count, j.failure_count
  from user_scheduler_jobs j
  left join user_scheduler_programs  p on p.program_name  = j.program_name
  left join user_scheduler_schedules s on s.schedule_name = j.schedule_name
 where j.job_name = 'ORDER_QUEUE_JOB';

For a job built on a schedule, USER_SCHEDULER_JOBS.REPEAT_INTERVAL is empty. The interval is kept in USER_SCHEDULER_SCHEDULES. If you only look at the jobs view, you’ll think the job has no timetable.

STATE is the column to read first. The values I saw in these tests:

Enable and disable: always the job#

exec dbms_scheduler.disable('ORDER_QUEUE_JOB')
exec dbms_scheduler.enable('ORDER_QUEUE_JOB')

-- several jobs in one call: a comma-separated list
exec dbms_scheduler.enable('ORDER_QUEUE_JOB, ORDER_QUEUE2_JOB')

disable keeps the job and all its settings. It just won’t run until you enable it again.

You can’t disable a job while it’s running:

ORA-27478: job "AILAB"."DEMO_LONG_JOB" is running

disable(..., force => true) works while the job runs. In my test the job became ENABLED = FALSE at once. The current run carried on to the end (its last line still wrote its row 20 seconds later), and no new run started after that. To end the current run itself, use stop_job, below.

Don’t disable the program to stop a job. Without force it’s refused (ORA-27479 again, because the job depends on it). With force the program is disabled, but the job stays ENABLED and SCHEDULED. At every run time it then fails:

ORA-27367: program "AILAB"."ORDER_QUEUE_PRG" associated with this job is disabled

Each of those counts as a failure, so with max_failures set the job ends up BROKEN. Turning jobs on and off is what enable and disable on the job are for.

Change the interval#

With a schedule, the interval belongs to the schedule:

exec dbms_scheduler.set_attribute('ORDER_QUEUE_SCH', 'repeat_interval', 'FREQ=SECONDLY;INTERVAL=30')

I had two jobs on this schedule. Both stayed enabled and both switched to the new interval, with nothing to do on the jobs themselves. That’s the point of a shared schedule, and also the thing to remember: changing a schedule changes every job that uses it.

A job made with repeat_interval directly in create_job (no schedule) is changed on the job instead:

exec dbms_scheduler.set_attribute('DEMO_TICK_JOB', 'repeat_interval', 'FREQ=MINUTELY;INTERVAL=5')

In my test that job’s next run moved from 15:00:37 to 15:04:37: five minutes after the original start, not five minutes after the change.

A typo is refused straight away, so you can’t save a timetable the scheduler can’t read:

-- 'FREQ=MINUTLY;INTERVAL=5'
ORA-27412: repeat interval or calendar contains invalid identifier: "AILAB"."MINUTLY"

Changing the interval didn’t make the job run early, and neither did disabling and enabling it again. I checked that with a job whose start date was a day away: no row appeared after any of those calls.

Change what the job runs#

With a program, the code belongs to the program:

exec dbms_scheduler.set_attribute('ORDER_QUEUE_PRG', 'program_action', 'PROCESS_ORDER_QUEUE_V2')

The job stayed enabled, and its next run already called the new procedure. The same set_attribute call changes any other attribute of a job, program or schedule: comments, max_failures, start_date, end_date…

Write the timetable: repeat_interval#

The calendar syntax is FREQ=... followed by optional INTERVAL= and BY... parts. Here are the ones I use most, with the next four runs the database calculated for each one (starting Tuesday 29 September, 14:00):

BYMONTHDAY=-1 means the last day of the month, whatever its length.

Always write BYMINUTE and BYSECOND when you use BYHOUR. In my test FREQ=DAILY;BYHOUR=2 on its own also gave 02:00, because the start time was exactly on the hour. Without those parts, the minutes and seconds come from the start date, so a schedule started at 14:37:12 would run at 02:37:12.

Preview a timetable before you use it#

evaluate_calendar_string returns the next run after a given time. Call it in a loop to see several runs. This is how I made the table above:

set serveroutput on
declare
  l_start timestamp with time zone := systimestamp at time zone 'Africa/Cairo';
  l_after timestamp with time zone := l_start;
  l_next  timestamp with time zone;
begin
  for i in 1 .. 5 loop
    dbms_scheduler.evaluate_calendar_string(
      calendar_string   => 'FREQ=WEEKLY;BYDAY=SUN,WED;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
      start_date        => l_start,
      return_date_after => l_after,
      next_run_date     => l_next);
    dbms_output.put_line(to_char(l_next, 'Dy DD Mon YYYY HH24:MI TZR'));
    l_after := l_next;
  end loop;
end;
/

Nothing is created, so you can try as many strings as you like.

The start date and the time zone trap#

The schedule’s time zone comes from its start_date, and how you write the start date makes a difference:

A fixed offset doesn’t know about daylight saving time. Cairo is at +03:00 until the end of October 2026 and +02:00 after that. I asked the database for the runs of a “daily at 02:00” timetable around that change, once with each kind of start date:

With the fixed offset, the nightly job moves to 01:00 local time for the whole winter.

For the queue job above, systimestamp is fine: every 10 seconds is every 10 seconds, whatever the clock says. For anything that runs at a local clock time, give start_date a region name. To move a schedule to 02:00 every night starting tomorrow:

begin
  dbms_scheduler.set_attribute('NIGHTLY_SCH', 'start_date',
    to_timestamp_tz('2026-09-30 02:00 Africa/Cairo', 'YYYY-MM-DD HH24:MI TZR'));
  dbms_scheduler.set_attribute('NIGHTLY_SCH', 'repeat_interval',
    'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
end;
/

If you leave start_date out, you get the time zone of the session that ran the script. That depends on the client, so don’t rely on it.

Run a job now#

-- in your own session: you wait for it, and an error comes straight back to you
exec dbms_scheduler.run_job('ORDER_QUEUE_JOB', use_current_session => true)

-- in the background, like a scheduled run
exec dbms_scheduler.run_job('ORDER_QUEUE_JOB', use_current_session => false)

use_current_session => true is the one to use while testing. Note one difference: in my test the background run added 1 to RUN_COUNT, and the run in my own session didn’t. Both appeared in the run history. The one in my session was logged with REASON="manually run".

Stop a running job#

exec dbms_scheduler.stop_job('ORDER_QUEUE_JOB')

This ends the current run. The run history shows it as STOPPED. The job stays enabled: in my test it went back to SCHEDULED with its next run already set. If you want it to stop and not run again, stop it and then disable it.

When a job fails#

The run history is in USER_SCHEDULER_JOB_RUN_DETAILS. The ERRORS column holds the error stack:

select job_name, actual_start_date, status, error#, errors
  from user_scheduler_job_run_details
 where job_name = 'ORDER_QUEUE_JOB'
 order by log_id desc;

This is what it showed for a test job whose code raises ORA-20001:

JOB_NAME       STATUS   ERROR#  ERRORS
DEMO_FAIL_JOB  FAILED   20001   ORA-20001: demo failure
                                ORA-06512: at line 1

USER_SCHEDULER_JOB_LOG has one line per event (RUN, BROKEN…), which is handy for a timeline.

max_failures turns a failing job off. I set it to 2 on a job that fails every minute. After the second failure the job was ENABLED = FALSE, STATE = BROKEN, and the log had a BROKEN line. To bring it back, fix the cause, then call enable. After enable, FAILURE_COUNT was back to 0 and the next run succeeded. For a job that runs every 10 seconds, max_failures is what stops a broken job from filling the log with failures all night.

Logging: LOGGING_FAILED_RUNS on the job didn’t stop the logging#

A job that runs every 10 seconds runs 8,640 times a day, and you don’t want a history row for each successful run. The obvious setting is on the job:

exec dbms_scheduler.set_attribute('ORDER_QUEUE_JOB', 'logging_level', dbms_scheduler.logging_failed_runs)

I tested it. The job showed LOGGING_LEVEL = FAILED RUNS. It ran 3 times in 35 seconds and all 3 successful runs were in USER_SCHEDULER_JOB_RUN_DETAILS. Setting the job to logging_off gave the same result.

The reason is the job’s class. Every job belongs to one, and by default that’s DEFAULT_JOB_CLASS, which logs RUNS. In my tests the class’s level won over the job’s lower one. Compare the two:

select j.job_name, j.logging_level as job_level, c.logging_level as class_level
  from user_scheduler_jobs j
  join all_scheduler_job_classes c on c.job_class_name = j.job_class
 where j.job_name = 'ORDER_QUEUE_JOB';

JOB_NAME         JOB_LEVEL    CLASS_LEVEL
ORDER_QUEUE_JOB  FAILED RUNS  RUNS

To really log only failures, the job would have to belong to a class that logs only failures. I couldn’t test that: creating a job class needs the MANAGE SCHEDULER privilege, which my test schema doesn’t have. If your DBA creates one, set_attribute('ORDER_QUEUE_JOB', 'job_class', '<that class>') moves the job into it. Check the result with the query above, and count the rows in USER_SCHEDULER_JOB_RUN_DETAILS after a few runs.

The job runs in its own session#

A background job runs in a separate database session. Two things follow from that, and I tested both.

Package variables from your session aren’t there. I set a package variable in my session, then started a job that wrote the variable’s value into the log table. It wrote job sees: (null). That’s why the example is a queue: a trigger or a page writes a row with status = 'PENDING', and the job reads the table, does the work and marks the row done. Memory never crosses from one session to the other.

At the end of a run, the work is committed or rolled back as a whole. A PLSQL_BLOCK job that inserted a row with no commit still left the row in the table: the job committed at the end. A block that inserted a row and then raised an error left nothing: the insert was rolled back. If you need part of the work kept after an error, commit it yourself, or handle the error inside the procedure.

A quick one-off job#

For a single job that doesn’t need its own program and schedule, create_job takes the action and the timetable directly:

begin
  dbms_scheduler.create_job(
    job_name        => 'DEMO_TICK_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'begin process_order_queue; end;',
    start_date      => systimestamp at time zone 'Africa/Cairo',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
    enabled         => true);
end;
/

job_type is what job_action holds: PLSQL_BLOCK takes an anonymous block as text, and STORED_PROCEDURE takes a procedure name. Everything else in this guide (enable, disable, set_attribute, run, stop, the logging) works the same. The only difference is that the interval and the code are attributes of the job itself.

Who owns the job#

A job belongs to the schema that calls create_job, unless you put a schema in the name ('OTHER_SCHEMA.MY_JOB', which needs the CREATE ANY JOB privilege). Some tools generate SYS.DBMS_SCHEDULER.CREATE_JOB(...). The SYS. there only names the package’s owner. The job still belongs to whoever runs the block. I saw that at work, where a job created this way was owned by the application schema.

Drop a job#

To remove everything, run the drop part of the script: the job with force => true, then the schedule and the program without it. On its own:

exec dbms_scheduler.drop_job('ORDER_QUEUE_JOB')

Like disable, drop_job without force refused a job that was running (ORA-27478).

Cheat sheet#

Rules I follow#

• Program + schedule + job, in one script that can be re-run. Drop the job first, in an order you write out.
• force => true on the job only. On a shared schedule or program it quietly disables other people’s jobs.
• Create the job disabled, set its attributes, then enable it. And auto_drop => false.
• Turn jobs on and off with the job, never with the program.
• Give start_date a region name (systimestamp at time zone 'Africa/Cairo') for anything that runs at a local clock time.
• Preview every new repeat_interval with evaluate_calendar_string.
• Don’t trust LOGGING_FAILED_RUNS on the job alone. Check the class’s logging level too.
• Hand work to a job through a table, never through package variables.
• Set max_failures on frequent jobs, so a broken job stops instead of failing all night.