Task Automation with the Scheduler

The Role of Automation in Database Administration

The Oracle Scheduler is a sophisticated job scheduling system that enables DBAs to automate routine, repetitive, and complex database tasks. It extends the capabilities of the legacy DBMS_JOB package with a richer, more flexible architecture that supports complex scheduling rules, event-driven execution, resource management, and integration with external systems. Automating tasks reduces human error, ensures consistency, and frees the DBA for higher-value strategic activities.

The economic justification for scheduling automation follows directly from the cost structure of manual operations. Let $C_m$ be the cost of manual task execution (DBA labor time), $C_a$ be the cost of developing and maintaining automated scheduling, and $n$ be the number of executions over the planning horizon. Automation dominates when $C_m \cdot n > C_a + c_a \cdot n$, where $c_a$ is the marginal cost per automated execution (approaching zero). For tasks with high frequency or high manual error rates, the case for automation is overwhelming.

Scheduler Architecture

The Oracle Scheduler consists of several architectural components:

• Job: The fundamental unit of work. A job specifies what action to execute (PL/SQL block, stored procedure, external executable), when to execute it (schedule), and optionally, additional attributes such as priority, resource consumption limits, and destination.
• Schedule: Defines the timing of job execution. Schedules can be time-based (e.g., "every day at 3:00 AM") or event-based (e.g., "when file X arrives in directory Y").
• Program: A reusable, named definition of the executable action, separate from the scheduling parameters. Programs can be referenced by multiple jobs, promoting modularity and reuse.
• Window: A time interval during which specific resource plans are active. Windows are used to allocate resources for maintenance tasks during off-peak hours.
• Window Group: A collection of windows that can be treated as a unit for resource allocation purposes.
• Job Class: Defines a category of jobs that share resource allocation attributes. Jobs within a class can be assigned to a specific resource consumer group.
• Chain: A workflow of interdependent steps, where each step can be a program, another chain, or an event. Chains enable complex batch processing pipelines.

Scheduler Workflow

The typical workflow for creating and managing scheduled jobs follows these steps:

1. Create a Program (optional): Define the executable action as a reusable entity.
2. Create a Schedule (optional): Define the timing as a reusable entity.
3. Create a Job: Combine the program and schedule (or inline definitions) into a job.
4. Enable the Job: Activate the job so it runs according to its schedule.
5. Monitor Execution: Check job logs and status.
6. Manage Lifecycle: Disable, enable, modify, or drop the job as needed.

Creating and Managing Jobs

Jobs are created using the DBMS_SCHEDULER.CREATE_JOB procedure. The simplest form combines an inline action and schedule:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'PURGE_AUDIT_LOG',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DELETE FROM audit_log WHERE log_date < SYSDATE - 30; COMMIT; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Purge audit log entries older than 30 days'
    );
END;

Key attributes of a job:

• job_type: The type of action — PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE (external OS command), CHAIN, SQL_SCRIPT, BACKUP_SCRIPT.
• job_action: The actual code or program name to execute.
• start_date: When the job becomes eligible for the first execution.
• repeat_interval: A calendaring expression defining the recurrence pattern.
• end_date: When the job ceases to be eligible for execution.
• enabled: Whether the job is immediately active.
• auto_drop: Whether the job is automatically dropped after completion (for one-time jobs).
• max_runs, max_failures: Limits on execution count or failures.

Managing Job Lifecycle

-- Disable a job
BEGIN
    DBMS_SCHEDULER.DISABLE('PURGE_AUDIT_LOG');
END;

-- Enable a job
BEGIN
    DBMS_SCHEDULER.ENABLE('PURGE_AUDIT_LOG');
END;

-- Run a job immediately (outside its schedule)
BEGIN
    DBMS_SCHEDULER.RUN_JOB('PURGE_AUDIT_LOG');
END;

-- Stop a running job
BEGIN
    DBMS_SCHEDULER.STOP_JOB('PURGE_AUDIT_LOG');
END;

-- Drop a job
BEGIN
    DBMS_SCHEDULER.DROP_JOB('PURGE_AUDIT_LOG');
END;

-- Drop a job forcefully (stop running instances first)
BEGIN
    DBMS_SCHEDULER.DROP_JOB('PURGE_AUDIT_LOG', FORCE => TRUE);
END;

Creating Reusable Schedules

Schedules decouple the timing logic from the job definition, enabling reuse across multiple jobs:

-- Create a named schedule
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE (
        schedule_name   => 'DAILY_MAINTENANCE_SCHEDULE',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0; BYSECOND=0',
        comments        => 'Daily maintenance window at 3:00 AM'
    );
END;

-- Create a job using a named schedule
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'COLLECT_STATS',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'DBMS_STATS.GATHER_SCHEMA_STATS(''HR'')',
        schedule_name   => 'DAILY_MAINTENANCE_SCHEDULE',
        enabled         => TRUE
    );
END;

Creating Reusable Programs

Programs encapsulate the executable action, separating the "what" from the "when":

-- Create a program with arguments
BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM (
        program_name   => 'GATHER_SCHEMA_STATS_PROG',
        program_type   => 'STORED_PROCEDURE',
        program_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
        number_of_arguments => 1,
        enabled        => FALSE
    );

    -- Define the argument
    DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
        program_name      => 'GATHER_SCHEMA_STATS_PROG',
        argument_position => 1,
        argument_name     => 'schema_name',
        argument_type     => 'VARCHAR2',
        default_value     => 'HR'
    );

    DBMS_SCHEDULER.ENABLE('GATHER_SCHEMA_STATS_PROG');
END;

-- Create a job using the program and schedule
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name      => 'COLLECT_HR_STATS',
        program_name  => 'GATHER_SCHEMA_STATS_PROG',
        schedule_name => 'DAILY_MAINTENANCE_SCHEDULE',
        enabled       => TRUE
    );
END;