The Evolution of Scheduling#

Historically, Oracle developers used DBMS_JOB to run background tasks. DBMS_JOB is fundamentally flawed—it lacks timezone support, cannot run external OS scripts, and makes dependency chaining nearly impossible.

Starting in Oracle 10g, Oracle introduced DBMS_SCHEDULER. It is an enterprise-grade scheduling engine built natively into the database. It handles complex calendaring, resource plans, job prioritization, and dependency chains.

Creating a Simple Job#

If you just need to run a PL/SQL procedure every night at midnight, you can create a standalone Job in a single call.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'NIGHTLY_INVENTORY_SYNC',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'pkg_inventory.sync_stock',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE,
    comments        => 'Syncs inventory from external POS nightly.'
  );
END;
/

By setting enabled => TRUE, the job begins executing based on the repeat_interval immediately.

Modular Scheduling (Programs & Schedules)#

For enterprise applications, creating standalone jobs is an anti-pattern. If you have 50 jobs that all run at "End of Month", and the business changes the definition of "End of Month", you have to update 50 jobs.

Instead, decouple the "WHAT" from the "WHEN".

1. Create a Schedule (The "WHEN")

BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'END_OF_MONTH_SCHED',
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=-1', -- -1 means last day of month
    comments        => 'Runs on the last day of every month'
  );
END;
/

2. Create a Program (The "WHAT")

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'PURGE_LOGS_PROG',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'pkg_admin.purge_old_logs',
    enabled        => TRUE
  );
END;
/

3. Bind them into a Job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'EOM_PURGE_JOB',
    program_name  => 'PURGE_LOGS_PROG',
    schedule_name => 'END_OF_MONTH_SCHED',
    enabled       => TRUE
  );
END;
/

The Calendaring Syntax#

Oracle's repeat interval syntax is incredibly powerful, acting like "Cron on steroids."

• Every 5 minutes: FREQ=MINUTELY; INTERVAL=5
• Every Monday at 6:30 AM: FREQ=WEEKLY; BYDAY=MON; BYHOUR=6; BYMINUTE=30
• Last Friday of every month: FREQ=MONTHLY; BYDAY=-1FRI
• Specific dates (e.g. End of Financial Quarters): FREQ=YEARLY; BYMONTH=3,6,9,12; BYMONTHDAY=-1

Building Job Chains (Dependencies)#

Often, Job B cannot start until Job A finishes successfully. If Job A fails, Job C should run to alert the admin. You build this using a Job Chain.

1. Create a Chain: DBMS_SCHEDULER.CREATE_CHAIN('ETL_CHAIN').
2. Add Steps to the Chain (Programs): DBMS_SCHEDULER.DEFINE_CHAIN_STEP.
3. Define the Rules (The Logic):
   • IF (Step_A COMPLETED) THEN START Step_B
   • IF (Step_A FAILED) THEN START Alert_Step

Common Gotchas#

Key Takeaways#