Oracle DBMS_SCHEDULER - Create & Manage Database Jobs

DBMS_SCHEDULER replaced DBMS_JOB in Oracle 10g and provides a complete enterprise job scheduling framework directly inside the database. It supports complex schedules, job chains, external executables, resource groups, email notifications, and detailed run history. This guide covers everything from simple one-off jobs to multi-step chains with error handling.

Overview

Key Concepts

Concept	Description
Job	The schedulable unit of work
Program	Reusable definition of what to execute
Schedule	Reusable definition of when to execute
Chain	A sequence of steps with dependency rules
Job Class	Groups jobs for resource management
Window	A time period with resource plan attached
Credential	OS credentials for external jobs

Creating Jobs

Simple PL/SQL Block Job

-- Simplest possible job - PL/SQL block with inline schedule
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'PURGE_AUDIT_LOG',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN purge_old_audit_records(30); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    end_date        => NULL,          -- Run forever
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments        => 'Purges audit log rows older than 30 days'
  );
END;
/

Stored Procedure Job

-- Job that calls a stored procedure with arguments
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'GATHER_SCHEMA_STATS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'DBMS_STATS.GATHER_SCHEMA_STATS',
    number_of_arguments => 1,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=22',
    enabled         => FALSE,         -- Enable after setting arguments
    auto_drop       => FALSE
  );

  -- Set the argument (positional)
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name       => 'GATHER_SCHEMA_STATS',
    argument_position => 1,
    argument_value => 'MYAPP'
  );

  DBMS_SCHEDULER.ENABLE('GATHER_SCHEMA_STATS');
END;
/

External Executable Job

-- Run an OS shell script or binary
-- Requires a credential and the Scheduler agent on the target host
BEGIN
  -- Create credential first
  DBMS_SCHEDULER.CREATE_CREDENTIAL(
    credential_name => 'ORACLE_OS_CRED',
    username        => 'oracle',
    password        => 'os_password'
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'BACKUP_SCRIPT',
    job_type        => 'EXECUTABLE',
    job_action      => '/dba/scripts/rman_backup.sh',
    credential_name => 'ORACLE_OS_CRED',
    destination     => 'localhost',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=0',
    enabled         => TRUE,
    auto_drop       => FALSE
  );
END;
/

Repeat Interval Syntax

The repeat_interval uses a calendaring expression language. Understanding the syntax is essential for correct scheduling.

Frequency Keywords

-- Every 5 minutes
'FREQ=MINUTELY; INTERVAL=5'

-- Every hour on the hour
'FREQ=HOURLY; BYMINUTE=0'

-- Daily at 2:30 AM
'FREQ=DAILY; BYHOUR=2; BYMINUTE=30; BYSECOND=0'

-- Every weekday at 6 AM
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=6'

-- Weekly on Saturday at 11 PM
'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=23; BYMINUTE=0'

-- Monthly on the last day at midnight
'FREQ=MONTHLY; BYMONTHDAY=-1; BYHOUR=0'

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

-- Quarterly (every 3 months)
'FREQ=MONTHLY; INTERVAL=3; BYMONTHDAY=1; BYHOUR=1'

-- Validate a repeat_interval without creating a job
SELECT DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
  'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
  SYSTIMESTAMP,
  NULL,
  10  -- Show next 10 run times
) FROM DUAL;

-- Correct way to preview next run times
DECLARE
  l_next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
    calendar_string    => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    start_date         => SYSTIMESTAMP,
    return_date_after  => SYSTIMESTAMP,
    next_run_date      => l_next_run_date
  );
  DBMS_OUTPUT.PUT_LINE('Next run: ' || l_next_run_date);
END;
/

Programs and Schedules (Reusable Components)

Creating a Reusable Program

-- Define the program once, reference it from multiple jobs
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name        => 'REFRESH_MATERIALIZED_VIEWS',
    program_type        => 'PLSQL_BLOCK',
    program_action      => '
      BEGIN
        FOR mv IN (SELECT mview_name FROM user_mviews WHERE refresh_mode = ''DEMAND'')
        LOOP
          DBMS_MVIEW.REFRESH(mv.mview_name, ''C'');
        END LOOP;
      END;',
    enabled             => TRUE,
    comments            => 'Refreshes all demand-mode materialized views'
  );
END;
/

-- Create a reusable schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name   => 'BUSINESS_HOURS_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,8,9,10,11,12,13,14,15,16,17',
    end_date        => NULL,
    comments        => 'Every 15 minutes during business hours Mon-Fri 07:00-18:00'
  );
END;
/

-- Create a job referencing both
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name      => 'MV_REFRESH_BUSINESS_HOURS',
    program_name  => 'REFRESH_MATERIALIZED_VIEWS',
    schedule_name => 'BUSINESS_HOURS_SCHEDULE',
    enabled       => TRUE,
    auto_drop     => FALSE
  );
END;
/

Job Chains

Chains allow you to define multi-step workflows with conditional branching based on step results.

Building a Simple Chain

-- Step 1: Create the chain
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN(
    chain_name => 'ETL_PIPELINE',
    comments   => 'Extract, transform, load pipeline'
  );
END;
/

-- Step 2: Define chain steps (each step references a program)
BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name  => 'ETL_PIPELINE',
    step_name   => 'EXTRACT',
    program_name => 'EXTRACT_SOURCE_DATA'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name  => 'ETL_PIPELINE',
    step_name   => 'TRANSFORM',
    program_name => 'TRANSFORM_DATA'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name  => 'ETL_PIPELINE',
    step_name   => 'LOAD',
    program_name => 'LOAD_TARGET_TABLE'
  );
END;
/

-- Step 3: Define rules (what triggers each step)
BEGIN
  -- Start EXTRACT immediately when chain starts
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'ETL_PIPELINE',
    condition  => 'TRUE',
    action     => 'START EXTRACT',
    rule_name  => 'START_EXTRACT'
  );

  -- Start TRANSFORM after EXTRACT succeeds
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'ETL_PIPELINE',
    condition  => 'EXTRACT COMPLETED SUCCESSFULLY',
    action     => 'START TRANSFORM',
    rule_name  => 'AFTER_EXTRACT'
  );

  -- Start LOAD after TRANSFORM succeeds
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'ETL_PIPELINE',
    condition  => 'TRANSFORM COMPLETED SUCCESSFULLY',
    action     => 'START LOAD',
    rule_name  => 'AFTER_TRANSFORM'
  );

  -- End the chain when LOAD completes (success or failure)
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'ETL_PIPELINE',
    condition  => 'LOAD COMPLETED',
    action     => 'END',
    rule_name  => 'END_CHAIN'
  );
END;
/

-- Step 4: Enable the chain
DBMS_SCHEDULER.ENABLE('ETL_PIPELINE');

-- Step 5: Create a job to run the chain
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'RUN_ETL_PIPELINE',
    job_type        => 'CHAIN',
    job_action      => 'ETL_PIPELINE',
    repeat_interval => 'FREQ=DAILY; BYHOUR=1',
    enabled         => TRUE,
    auto_drop       => FALSE
  );
END;
/

Monitoring Jobs

Current Job Status

-- All scheduled jobs with next run time
SELECT
  owner,
  job_name,
  job_type,
  state,
  enabled,
  TO_CHAR(last_start_date, 'DD-MON-YY HH24:MI:SS')  AS last_start,
  TO_CHAR(last_run_duration, 'HH24:MI:SS')           AS last_duration,
  TO_CHAR(next_run_date, 'DD-MON-YY HH24:MI:SS')    AS next_run,
  run_count,
  failure_count,
  retry_count
FROM dba_scheduler_jobs
ORDER BY next_run_date;

-- Jobs currently running
SELECT
  owner,
  job_name,
  session_id,
  running_instance,
  TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS started,
  ROUND((SYSTIMESTAMP - actual_start_date) * 24 * 60, 1) AS running_mins,
  cpu_used,
  status
FROM dba_scheduler_running_jobs
ORDER BY actual_start_date;

Job Run History

-- Last 30 days of job run history
SELECT
  owner,
  job_name,
  TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI:SS')  AS started,
  TO_CHAR(run_duration, 'HH24:MI:SS')                   AS duration,
  status,
  error#,
  additional_info
FROM dba_scheduler_job_run_details
WHERE actual_start_date > SYSDATE - 30
ORDER BY actual_start_date DESC;

-- Failed jobs in the last 7 days with error details
SELECT
  owner,
  job_name,
  TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS failed_at,
  error#,
  SUBSTR(additional_info, 1, 200)                      AS error_info
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
  AND actual_start_date > SYSDATE - 7
ORDER BY actual_start_date DESC;

-- Average run duration trend per job (last 30 runs each)
SELECT
  job_name,
  COUNT(*)                                          AS run_count,
  TO_CHAR(AVG(EXTRACT(SECOND FROM run_duration) +
              EXTRACT(MINUTE FROM run_duration) * 60 +
              EXTRACT(HOUR FROM run_duration) * 3600), 'FM9999')
                                                    AS avg_duration_secs,
  MAX(EXTRACT(SECOND FROM run_duration) +
      EXTRACT(MINUTE FROM run_duration) * 60 +
      EXTRACT(HOUR FROM run_duration) * 3600)       AS max_duration_secs,
  SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failures
FROM dba_scheduler_job_run_details
WHERE actual_start_date > SYSDATE - 30
GROUP BY job_name
ORDER BY avg_duration_secs DESC NULLS LAST;

Managing Jobs

Enable, Disable, and Run

-- Enable a job
EXEC DBMS_SCHEDULER.ENABLE('SCHEMA.JOB_NAME');

-- Disable a job (preserves the job definition)
EXEC DBMS_SCHEDULER.DISABLE('SCHEMA.JOB_NAME');

-- Run a job immediately (asynchronous - returns immediately)
EXEC DBMS_SCHEDULER.RUN_JOB('SCHEMA.JOB_NAME');

-- Run synchronously (waits for completion)
EXEC DBMS_SCHEDULER.RUN_JOB('SCHEMA.JOB_NAME', use_current_session => TRUE);

-- Stop a running job
EXEC DBMS_SCHEDULER.STOP_JOB('SCHEMA.JOB_NAME', force => FALSE);
EXEC DBMS_SCHEDULER.STOP_JOB('SCHEMA.JOB_NAME', force => TRUE);  -- Kill if needed

-- Drop a job
EXEC DBMS_SCHEDULER.DROP_JOB('SCHEMA.JOB_NAME');
EXEC DBMS_SCHEDULER.DROP_JOB('SCHEMA.JOB_NAME', force => TRUE);  -- Even if running

Modify Job Attributes

-- Change repeat interval
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'PURGE_AUDIT_LOG',
    attribute => 'repeat_interval',
    value     => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0'
  );
END;
/

-- Change max run duration (automatically kills job after limit)
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'PURGE_AUDIT_LOG',
    attribute => 'max_run_duration',
    value     => INTERVAL '2' HOUR
  );
END;
/

-- Set number of retries on failure
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'ETL_LOAD_JOB',
    attribute => 'restartable',
    value     => TRUE
  );
END;
/

Email Notifications

-- Configure email for job failure notifications (requires ACL / UTL_MAIL setup)
BEGIN
  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
    job_name   => 'ETL_PIPELINE_JOB',
    recipients => '[email protected]',
    sender     => '[email protected]',
    subject    => 'Scheduler Job %job_name% %event_type%',
    body       => 'Job: %job_name%' || CHR(10) ||
                  'Status: %event_type%' || CHR(10) ||
                  'Error: %error_message%' || CHR(10) ||
                  'Run Time: %run_duration%',
    filter_condition => 'job_failed_count > 0',
    events     => DBMS_SCHEDULER.JOB_FAILED_EVENT +
                  DBMS_SCHEDULER.JOB_BROKEN_EVENT
  );
END;
/

-- Remove email notification
BEGIN
  DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION(
    job_name   => 'ETL_PIPELINE_JOB',
    recipients => '[email protected]'
  );
END;
/

Lightweight Jobs (12c+)

Lightweight jobs are a 12c feature optimised for high-frequency scheduling. They have lower overhead than standard jobs and are ideal when you need thousands of jobs that reference the same program.

-- Create a program for lightweight jobs to reference
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => 'HEARTBEAT_CHECK',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN record_heartbeat(:1); END;',
    number_of_arguments => 1,
    enabled        => TRUE
  );
END;
/

-- Create a lightweight job (must reference a program, not inline)
BEGIN
  DBMS_SCHEDULER.CREATE_JOBS(
    jobs => SYS.DBMS_SCHEDULER_JOB_ARR(
      SYS.DBMS_SCHEDULER_JOB(
        job_name        => 'LW_HEARTBEAT_NODE1',
        program_name    => 'HEARTBEAT_CHECK',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
        enabled         => TRUE,
        job_style       => 'LIGHTWEIGHT'
      )
    )
  );
END;
/

Job Classes and Resource Management

-- Create a job class linked to a consumer group
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name          => 'BATCH_JOB_CLASS',
    resource_consumer_group => 'BATCH_GROUP',
    logging_level           => DBMS_SCHEDULER.LOGGING_RUNS,
    log_history             => 30,
    comments                => 'Class for batch processing jobs'
  );
END;
/

-- Assign a job to the class
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'ETL_PIPELINE_JOB',
    attribute => 'job_class',
    value     => 'BATCH_JOB_CLASS'
  );
END;
/

-- View job classes
SELECT
  job_class_name,
  resource_consumer_group,
  service,
  logging_level,
  log_history,
  comments
FROM dba_scheduler_job_classes;

Troubleshooting

Job Not Running

-- Check job state and any blocking conditions
SELECT
  job_name,
  state,
  enabled,
  broken,
  failure_count,
  TO_CHAR(last_start_date, 'DD-MON-YY HH24:MI:SS') AS last_run,
  TO_CHAR(next_run_date, 'DD-MON-YY HH24:MI:SS')   AS next_run,
  schedule_type,
  repeat_interval
FROM dba_scheduler_jobs
WHERE job_name = 'MY_JOB';

-- Check Scheduler agent and coordinator
SELECT
  component_name,
  status,
  description
FROM v$scheduler_running_jobs;

-- Check for Scheduler windows blocking jobs
SELECT
  window_name,
  enabled,
  active,
  TO_CHAR(next_start_date, 'DD-MON-YY HH24:MI:SS') AS next_start,
  duration,
  resource_plan
FROM dba_scheduler_windows
ORDER BY next_start_date;

Job Marked Broken

A job becomes broken after max_failures consecutive failures (default: unlimited, but broken after a large number).

-- Reset a broken job
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'MY_JOB',
    attribute => 'max_failures',
    value     => 5
  );
END;
/

-- Re-enable a broken job
EXEC DBMS_SCHEDULER.ENABLE('MY_JOB');

-- View Scheduler log for errors
SELECT
  log_date,
  job_name,
  status,
  user_name,
  additional_info
FROM dba_scheduler_job_log
WHERE job_name = 'MY_JOB'
ORDER BY log_date DESC
FETCH FIRST 20 ROWS ONLY;

Best Practices

Always set auto_drop => FALSE - The default TRUE drops the job after it runs once. This surprises many DBAs.
Use named programs and schedules - Reusable components reduce duplication and make changes easier.
Set max_run_duration - Prevent runaway jobs from holding locks indefinitely.
Use logging_level => LOGGING_RUNS on job classes to retain run history.
Test with RUN_JOB(..., use_current_session => TRUE) - Synchronous execution surfaces errors immediately in the calling session.
Assign jobs to job classes - Enables resource management and prevents batch jobs from starving OLTP.
Monitor failure_count regularly - A rising failure count on a supposedly healthy job warrants investigation.

Oracle AWR Report Generation - Schedule automated AWR reports
Oracle Scheduler Scripts - Built-in Scheduler tool guides
Administration Scripts - DBA maintenance script library

Oracle DBMS_SCHEDULER - Create & Manage Database Jobs

Oracle DBMS_SCHEDULER - Create & Manage Database Jobs

Overview

Key Concepts

Creating Jobs

Simple PL/SQL Block Job

Stored Procedure Job

External Executable Job

Repeat Interval Syntax

Frequency Keywords

Programs and Schedules (Reusable Components)

Creating a Reusable Program

Job Chains

Building a Simple Chain

Monitoring Jobs

Current Job Status

Job Run History

Managing Jobs

Enable, Disable, and Run

Modify Job Attributes

Email Notifications

Lightweight Jobs (12c+)

Job Classes and Resource Management

Troubleshooting

Job Not Running

Job Marked Broken

Best Practices

Related Topics