DBMS_SCHEDULER is an advanced job scheduling and management package in Oracle that offers extensive features and flexibility. It is designed to schedule and manage a wide range of tasks, from simple PL/SQL blocks to complex job chains and external scripts. It was introduced in Oracle 10 and got many improvements until Oracle 12. Here is a summary of its most frequent tasks.

Key Features and Common Tasks

Creating Jobs

Schedule jobs to run PL/SQL blocks, procedures, or external executables.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'MY_SCHEDULED_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=10; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE
  );
END;
/

The repeat_interval parameter in DBMS_SCHEDULER specifies how frequently a job should run. Here are some examples and brief explanations:

# Daily at a Specific Time: This interval sets the job to run every day at 9:00 AM.
'FREQ=DAILY; BYHOUR=9; BYMINUTE=0; BYSECOND=0'


# Weekly on Specific Days: This interval schedules the job to run every Monday and Thursday at 9:00 AM.
'FREQ=WEEKLY; BYDAY=MON,THU; BYHOUR=9; BYMINUTE=0; BYSECOND=0'


# Monthly on a Specific Day: This interval sets the job to run on the 15th day of every month at 10:00 AM.
'FREQ=MONTHLY; BYMONTHDAY=15; BYHOUR=10; BYMINUTE=0; BYSECOND=0'


# Every Hour: This interval schedules the job to run every hour.
'FREQ=HOURLY; INTERVAL=1'


# Every 15 Minutes: This interval sets the job to run every 15 minutes.
'FREQ=MINUTELY; INTERVAL=15'


# Daily on Weekdays Only: This interval schedules the job to run every weekday at 8:00 AM.
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8; BYMINUTE=0; BYSECOND=0'


# Quarterly on Specific Months: This interval sets the job to run on the 1st day of January, April, July, and October at 12:00 PM.
'FREQ=MONTHLY; BYMONTH=1,4,7,10; BYMONTHDAY=1; BYHOUR=12; BYMINUTE=0; BYSECOND=0'


# Annually on a Specific Date: This interval schedules the job to run once a year on December 31st at 11:59 PM.
'FREQ=YEARLY; BYMONTH=12; BYMONTHDAY=31; BYHOUR=23; BYMINUTE=59; BYSECOND=0'

Creating Schedules:

Define complex and reusable schedules with specific intervals. This is useful for creating standard intervals that can be applied to multiple jobs.

BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'WEEKLY_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,THU; BYHOUR=9; BYMINUTE=0; BYSECOND=0'
  );
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'MY_WEEKLY_JOB',
    schedule_name   => 'WEEKLY_SCHEDULE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_procedure; END;',
    enabled         => TRUE
  );
END;
/

Managing Jobs:

Enable, disable, and drop jobs.

BEGIN
  DBMS_SCHEDULER.enable('MY_SCHEDULED_JOB');
  DBMS_SCHEDULER.disable('MY_SCHEDULED_JOB');
  DBMS_SCHEDULER.drop_job('MY_SCHEDULED_JOB');
END;
/

Forcing Job Execution:

Run a job immediately.

BEGIN
  DBMS_SCHEDULER.run_job('MY_SCHEDULED_JOB');
END;
/

Monitoring Jobs:

Check job status and execution logs.

SELECT 
  job_name,
  log_date,
  status,
  error#,
  additional_info
FROM 
  dba_scheduler_job_run_details
WHERE 
  job_name = 'MY_SCHEDULED_JOB'
ORDER BY 
  log_date DESC;

Creating Job Chains:

Job chains in Oracle’s DBMS_SCHEDULER is a powerful feature that allows you to define and manage a sequence of related jobs, specifying their execution order and dependencies. This is particularly useful for complex workflows where the completion of one task triggers the next.

# Create the Chain:
BEGIN
  DBMS_SCHEDULER.create_chain(
    chain_name => 'MY_JOB_CHAIN',
    rule_set_name => NULL,
    evaluation_interval => NULL,
    comments => 'Example job chain'
  );
END;
/

# Define Chain Steps:
BEGIN
  DBMS_SCHEDULER.define_chain_step(
    chain_name => 'MY_JOB_CHAIN',
    step_name => 'STEP_1',
    program_name => 'my_program_1'
  );
  DBMS_SCHEDULER.define_chain_step(
    chain_name => 'MY_JOB_CHAIN',
    step_name => 'STEP_2',
    program_name => 'my_program_2'
  );
END;
/

# Define Chain Rules:
BEGIN
  DBMS_SCHEDULER.define_chain_rule(
    chain_name => 'MY_JOB_CHAIN',
    condition => 'TRUE',
    action => 'START STEP_1'
  );
  DBMS_SCHEDULER.define_chain_rule(
    chain_name => 'MY_JOB_CHAIN',
    condition => 'STEP_1 COMPLETED',
    action => 'START STEP_2'
  );
END;
/

# Enable the Chain:
BEGIN
  DBMS_SCHEDULER.enable('MY_JOB_CHAIN');
END;
/

# Schedule or Start the Chain:
BEGIN
  DBMS_SCHEDULER.create_job(
    job_name => 'MY_CHAIN_JOB',
    job_type => 'CHAIN',
    job_action => 'MY_JOB_CHAIN',
    start_date => SYSTIMESTAMP,
    enabled => TRUE
  );
END;
/

The condition parameter in the DBMS_SCHEDULER.define_chain_rule procedure specifies the criteria under which a chain rule will be applied. The condition can reference the state of steps within the chain, such as whether a step has succeeded, failed, or completed. Here are some examples of conditions:

# Always True: This condition always evaluates to true, meaning the rule will always be applied.
'TRUE'

# Step Completed Successfully: This condition checks if a specific step has completed successfully.
'STEP_1 COMPLETED'

# Step Succeeded: This condition checks if a specific step has succeeded.
'STEP_1 SUCCEEDED'

# Step Failed: This condition checks if a specific step has failed.
'STEP_1 FAILED'

# Step Skipped: This condition checks if a specific step was skipped.
'STEP_1 SKIPPED'

# Multiple Steps Completed: This condition checks if multiple steps have completed.
'STEP_1 COMPLETED AND STEP_2 COMPLETED'

# One of Multiple Steps Succeeded: This condition checks if any one of multiple steps has succeeded.
'STEP_1 SUCCEEDED OR STEP_2 SUCCEEDED'

# Step Finished (either succeeded or failed): This condition checks if a specific step has finished, regardless of the outcome.
'STEP_1 FINISHED'

# Chained Conditions: This condition chains multiple conditions together with logical operators.
'(STEP_1 SUCCEEDED OR STEP_2 FAILED) AND STEP_3 COMPLETED'

The action parameter in the DBMS_SCHEDULER.define_chain_rule procedure specifies what action should be taken when the condition of the chain rule is met. This allows for the control of the flow of execution within a job chain, managing how and when each step in the chain is executed. The action parameter can direct the scheduler to start, end, pause, or skip steps, among other actions.

# START step_name: This action starts the specified step when the condition is met.
'START STEP_2'

# END: This action ends the chain execution when the condition is met.
'END'

# PAUSE: This action pauses the chain execution when the condition is met.
'PAUSE'

# SKIP step_name: This action skips the specified step when the condition is met.
'SKIP STEP_2'

# STOP: This action stops the entire chain execution immediately when the condition is met.
'STOP'

DBMS_SCHEDULER.create_event_schedule

The DBMS_SCHEDULER.create_event_schedule procedure in Oracle DBMS_SCHEDULER allows you to create a schedule that triggers based on specific events rather than at fixed time intervals. This feature is particularly useful for jobs that need to run in response to database events or external events, such as file arrivals, message queue notifications, or custom application events.

# Creating a Schedule Based on a Database Table Event:
BEGIN
  DBMS_SCHEDULER.create_event_schedule (
    schedule_name   => 'TABLE_INSERT_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    event_condition => 'tab.user = ''MY_USER'' AND tab.operation = ''INSERT'' AND tab.object_name = ''MY_TABLE''',
    queue_spec      => 'SYS.EVENT_QUEUE',
    comments        => 'Schedule for triggering on table insert'
  );
END;
/

# Creating a Schedule Based on a File Arrival Event:
BEGIN
  DBMS_SCHEDULER.create_event_schedule (
    schedule_name   => 'FILE_ARRIVAL_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    event_condition => 'SYS.FILE_WATCHER_EVENT%FILE_NAME = ''example.txt'' AND SYS.FILE_WATCHER_EVENT%FILE_PATH = ''/mydir''',
    queue_spec      => 'SYS.FILE_WATCHER_QUEUE',
    comments        => 'Schedule for triggering on file arrival'
  );
END;
/

# Creating a Schedule Based on a Message Queue Event:
BEGIN
  DBMS_SCHEDULER.create_event_schedule (
    schedule_name   => 'QUEUE_MESSAGE_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    event_condition => 'tab.user_data LIKE ''%MY_MESSAGE_CONTENT%''',
    queue_spec      => 'MY_MESSAGE_QUEUE',
    comments        => 'Schedule for triggering on message queue event'
  );
END;
/

Using the Event Schedule in a Job

After creating an event schedule, you can use it in a job definition to trigger the job based on the event. Here’s an example of creating a job that uses an event schedule:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'MY_EVENT_TRIGGERED_JOB',
    schedule_name   => 'TABLE_INSERT_SCHEDULE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_procedure; END;',
    enabled         => TRUE,
    comments        => 'Job triggered by table insert event'
  );
END;
/

In this example, the job MY_EVENT_TRIGGERED_JOB is triggered by the event defined in the TABLE_INSERT_SCHEDULE, which occurs when a row is inserted into the specified table by the specified user.

Summary

DBMS_SCHEDULER is a powerful tool for scheduling and managing jobs in Oracle. Its advanced features allow for detailed control over job execution, making it suitable for a wide range of applications from simple tasks to complex workflows.

Oracle 19c PL/SQL Packages and Types Reference