Scheduling Recurring PL/SQL Jobs With DBMS_SCHEDULER

Automating Repetitive Tasks

Many database applications need recurring tasks: nightly data purges, hourly report generation, periodic email notifications, or regular statistics gathering. Oracle’s DBMS_SCHEDULER package provides a powerful framework for scheduling and managing these jobs directly in the database without relying on external cron jobs or Windows Task Scheduler.

Creating a Simple Recurring Job

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'NIGHTLY_ORDER_CLEANUP',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN cleanup_old_orders(p_days_old => 90); END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => TRUE,
        comments        => 'Delete orders older than 90 days every night at 2 AM'
    );
END;
/

Common Schedule Patterns

-- Every 15 minutes during business hours on weekdays
'FREQ=MINUTELY; INTERVAL=15; BYHOUR=8,9,10,11,12,13,14,15,16,17; BYDAY=MON,TUE,WED,THU,FRI'

-- First Monday of every month at 6 AM
'FREQ=MONTHLY; BYDAY=1MON; BYHOUR=6; BYMINUTE=0'

-- Every Sunday at midnight
'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=0; BYMINUTE=0'

-- Every 4 hours
'FREQ=HOURLY; INTERVAL=4'

Monitoring Jobs

SELECT job_name, state, last_start_date, next_run_date, run_count, failure_count
  FROM user_scheduler_jobs;

SELECT job_name, status, actual_start_date, run_duration, additional_info
  FROM user_scheduler_job_run_details
 ORDER BY actual_start_date DESC
 FETCH FIRST 20 ROWS ONLY;

Error Handling in Scheduled Jobs

Scheduled jobs run without a user interface, so error handling is critical. Always wrap your job logic in an exception handler that logs errors to a table. Set the max_failures attribute to automatically disable a job after consecutive failures, preventing a broken job from consuming resources indefinitely.

Managing Jobs

DBMS_SCHEDULER.DISABLE('NIGHTLY_ORDER_CLEANUP');
DBMS_SCHEDULER.RUN_JOB('NIGHTLY_ORDER_CLEANUP');  -- run immediately for testing
DBMS_SCHEDULER.DROP_JOB('NIGHTLY_ORDER_CLEANUP');