Oracle Job Scheduling & Event-Based Automation: Jobs, Events, Chains & Calendars

TAKEAWAYS:

• Oracle Scheduler automates tasks via jobs, events, and chains using SQL, PL/SQL, or external programs.
• Jobs can run once, repeatedly, or conditionally (e.g., on data changes or system events).
• Job classes prioritize tasks, and job chains sequence dependent operations (e.g., backup → compress → notify).
• Calendars define valid execution windows (e.g., weekdays 9 AM–5 PM).
• Error handling includes retry logic, notifications, and fallback actions.
• Real-world examples: eSewa’s nightly transaction reconciliation, Daraz’s inventory sync jobs, and NTC’s network performance monitoring.

1. Introduction to Oracle Scheduler

Oracle Scheduler is a built-in automation engine that executes tasks (jobs) based on time, events, or conditions. It replaces older tools like DBMS_JOB and offers:

• Granular control over job execution (e.g., run every 15 minutes or only on Mondays).
• Dependency management via job chains (e.g., Job A must finish before Job B starts).
• Error resilience with retry mechanisms and notifications.
• Integration with Oracle Database 11g and later.

classDiagram
    class Scheduler {
        +Jobs: Tasks to execute (SQL, PL/SQL, external programs)
        +Events: Triggers (e.g., "after table update")
        +Chains: Ordered sequences of jobs
        +Calendars: Define valid execution windows
        +Job Classes: Priority queues (e.g., HIGH, MEDIUM, LOW)
    }
    class Job {
        -Name: Unique identifier
        -Program: SQL/PL/SQL/external
        -Schedule: WHEN (time/event) + REPEAT (frequency)
        -Enabled: ON/OFF flag
        -Error Handling: Retry count, notification
    }
    class Event {
        -Type: SYSTEM (e.g., "database startup"), DATABASE (e.g., "table change")
        -Condition: SQL query or DDL trigger
    }
    Scheduler "1" *-- "many" Job : "contains"
    Scheduler "1" *-- "many" Event : "triggers"
    Scheduler "1" *-- "many" Chains : "manages"
    Scheduler "1" *-- "many" Calendars : "applies"
    Scheduler "1" *-- "many" JobClasses : "assigns"

Key Components

classDiagram
    class Scheduler {
        +Jobs: Tasks to execute (SQL, PL/SQL, external programs)
        +Events: Triggers (e.g., "after table update")
        +Chains: Ordered sequences of jobs
        +Calendars: Define valid execution windows
        +Job Classes: Priority queues (e.g., HIGH, MEDIUM, LOW)
    }
    class Job {
        -Name: Unique identifier
        -Program: SQL/PL/SQL/external
        -Schedule: WHEN (time/event) + REPEAT (frequency)
        -Enabled: ON/OFF flag
        -Error Handling: Retry count, notification
    }
    class Event {
        -Type: SYSTEM (e.g., "database startup"), DATABASE (e.g., "table change")
        -Condition: SQL query or DDL trigger
    }
    Scheduler "1" *-- "many" Job
    Scheduler "1" *-- "many" Event
    Scheduler "1" *-- "many" Chains

Why Use Scheduler?

• Automate repetitive tasks: Nightly backups, log purges, or report generation.
• Reduce manual errors: Eliminate human intervention in critical processes.
• Optimize resources: Run low-priority jobs during off-peak hours.

2. Jobs: The Building Blocks

A job is a unit of work defined by:

1. Program: What to execute (SQL, PL/SQL, or an external script).
2. Schedule: When to run (time-based or event-based).
3. Job Class: Priority level (e.g., DEFAULT_JOB_CLASS, CRITICAL_JOB_CLASS).
4. Enabled: TRUE/FALSE to activate/deactivate.

Creating a Job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'nightly_backup_job',
    job_type        => 'SQL_SCRIPT',
    job_action      => 'EXECUTE backup_script.sql',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=0', -- Runs daily at 11 PM
    enabled         => TRUE,
    job_class       => 'DEFAULT_JOB_CLASS',
    comments        => 'Full database backup'
  );
END;
/

Worked Example: eSewa’s Transaction Reconciliation

Scenario: eSewa needs to reconcile transactions daily at 2 AM to ensure no discrepancies. Job Definition:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'esewa_reconciliation',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
      BEGIN
        FOR rec IN (SELECT * FROM pending_transactions WHERE status = ''unmatched'') LOOP
          -- Logic to match transactions with banks
          UPDATE transactions SET status = ''matched'' WHERE id = rec.id;
        END LOOP;
        -- Send email alert
        DBMS_SCHEDULER.RUN_JOB(''send_alert_job'');
      END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Daily reconciliation for eSewa transactions'
  );
END;
/

3. Events: Trigger-Based Automation

Events fire jobs in response to system or database changes. Types:

sequenceDiagram
    participant DB as Oracle Database
    participant Scheduler as Oracle Scheduler
    participant Job as Alert Job
    DB->>Scheduler: High CPU detected (v$system_event)
    Scheduler->>Job: Trigger 'high_cpu_event'
    Job->>DB: INSERT INTO alerts (message, timestamp)
    Job->>Admin: Send email notification
    Note right of Job: Event-based automation

Creating an Event-Based Job

-- Step 1: Create an event
BEGIN
  DBMS_SCHEDULER.CREATE_EVENT (
    event_name      => 'high_cpu_event',
    event_condition => 'SELECT 1 FROM v$system_event WHERE event = ''CPU time'', wait_time > 1000',
    event_handlers  => 'DBMS_SCHEDULER.RUN_JOB(''alert_admin_job'')',
    enabled         => TRUE
  );
END;
/
-- Step 2: Create the job to handle the event
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'alert_admin_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => '
      BEGIN
        INSERT INTO alerts (message, timestamp) VALUES (''High CPU detected!'', SYSTIMESTAMP);
        -- Send email to DBA team
      END;',
    enabled         => TRUE
  );
END;
/

Real-World Example: NTC’s Network Monitoring

• Event: High latency detected in v$network.
• Job: Trigger a script to reroute traffic or notify engineers.
• Code:

  BEGIN
    DBMS_SCHEDULER.CREATE_EVENT (
      event_name      => 'ntc_latency_alert',
      event_condition => 'SELECT 1 FROM ntc_metrics WHERE latency > 500',
      event_handlers  => 'DBMS_SCHEDULER.RUN_JOB(''reroute_traffic_job'')'
    );
  END;
  

4. Job Chains: Sequencing Tasks

Job chains group jobs into ordered sequences (e.g., Job A → Job B → Job C). Use cases:

• Backup workflow: Backup → Compress → Notify admin.
• Data migration: Extract → Transform → Load (ETL).
• Compliance checks: Audit → Log → Archive.

Creating a Chain

-- Step 1: Create individual jobs
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'job_backup',
    job_type        => 'EXECUTABLE',
    job_action      => 'backup.sh',
    enabled         => TRUE
  );
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'job_compress',
    job_type        => 'EXECUTABLE',
    job_action      => 'compress.sh',
    enabled         => TRUE
  );
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'job_notify',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DBMS_ALERT.SEND(''Backup complete!'')',
    enabled         => TRUE
  );
END;
/
-- Step 2: Create the chain
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name      => 'backup_chain',
    enabled         => TRUE,
    comments        => 'Full backup workflow'
  );
  -- Add jobs to the chain in order
  DBMS_SCHEDULER.SET_CHAIN_DEPENDENCY (
    chain_name      => 'backup_chain',
    job_name        => 'job_backup',
    dependent_job   => NULL -- First job in chain
  );
  DBMS_SCHEDULER.SET_CHAIN_DEPENDENCY (
    chain_name      => 'backup_chain',
    job_name        => 'job_compress',
    dependent_job   => 'job_backup'
  );
  DBMS_SCHEDULER.SET_CHAIN_DEPENDENCY (
    chain_name      => 'backup_chain',
    job_name        => 'job_notify',
    dependent_job   => 'job_compress'
  );
END;
/
-- Step 3: Schedule the chain
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    name  => 'backup_chain',
    attribute => 'repeat_interval',
    value  => 'FREQ=DAILY; BYHOUR=1'
  );
  DBMS_SCHEDULER.ENABLE('backup_chain');
END;
/

Visual: Job Chain Flow

flowchart TD
    A["Job: backup.sh\n(Runs at 1 AM)"] -->|"Completes"| B["Job: compress.sh"]
    B -->|"Completes"| C["Job: notify_admin\n(Sends email)"]

5. Calendars: Controlling Execution Windows

Calendars define when jobs can run (e.g., weekdays 9 AM–5 PM). Types:

• Predefined: SYSDBA_CALENDAR (always enabled), NEVER (disabled).
• Custom: Define your own (e.g., business_hours).

Creating a Calendar

BEGIN
  DBMS_SCHEDULER.CREATE_CALENDAR (
    calendar_name   => 'business_hours',
    comments        => 'Weekdays 9 AM to 5 PM'
  );
  -- Add working hours
  DBMS_SCHEDULER.SET_CALENDAR_ATTRIBUTE (
    name      => 'business_hours',
    attribute => 'day_use',
    value     => 'FREQ=DAILY; BYDAY=MO,TU,WE,TH,FR; BYHOUR=9-17'
  );
  -- Exclude holidays
  DBMS_SCHEDULER.SET_CALENDAR_ATTRIBUTE (
    name      => 'business_hours',
    attribute => 'exclude_dates',
    value     => 'TO_DATE(''2023-12-25'', ''YYYY-MM-DD'')' -- Christmas
  );
END;
/
-- Assign calendar to a job
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    name  => 'nightly_report_job',
    attribute => 'calendar_name',
    value  => 'business_hours'
  );
END;
/

Real-World Example: Daraz’s Inventory Sync

• Job: Sync inventory with suppliers at 3 AM.
• Calendar: Exclude weekends and holidays.
• Code:

  BEGIN
    DBMS_SCHEDULER.CREATE_CALENDAR (
      calendar_name   => 'daraz_sync_calendar',
      comments        => 'Weekdays 3 AM only'
    );
    DBMS_SCHEDULER.SET_CALENDAR_ATTRIBUTE (
      name      => 'daraz_sync_calendar',
      attribute => 'day_use',
      value     => 'FREQ=DAILY; BYDAY=MO,TU,WE,TH,FR; BYHOUR=3'
    );
    -- Assign to job
    DBMS_SCHEDULER.SET_ATTRIBUTE (
      name  => 'sync_inventory_job',
      attribute => 'calendar_name',
      value  => 'daraz_sync_calendar'
    );
  END;
  

6. Error Handling and Job Classes

Job Classes: Prioritizing Tasks

Jobs run in FIFO order within their class. Default classes:

Example:

-- Create a high-priority class
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
    job_class_name  => 'CRITICAL_JOB_CLASS',
    resource_consumer_group => 'CRITICAL_GROUP',
    enabled          => TRUE
  );
END;
/
-- Assign a job to it
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    name  => 'emergency_backup_job',
    attribute => 'job_class',
    value  => 'CRITICAL_JOB_CLASS'
  );
END;
/

Error Handling

Configure jobs to:

1. Retry on failure (e.g., 3 retries with 5-minute delays).
2. Notify admins via email or log entries.
3. Skip and continue the chain.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'fragile_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DELETE FROM temp_data;', -- Risky operation
    repeat_interval => 'FREQ=DAILY',
    enabled         => TRUE,
    auto_drop       => FALSE, -- Keep job definition
    max_run_duration => 'PT1H', -- Max runtime
    retry_count     => 3, -- Retry 3 times
    retry_delay     => 'PT5M', -- Wait 5 mins between retries
    job_handler     => 'dbms_scheduler.send_email_notification' -- Notify on failure
  );
END;
/

7. Monitoring and Managing Jobs

Key Views

Example Query:

SELECT job_name, state, enabled, next_run_date
FROM user_scheduler_jobs
WHERE enabled = 'TRUE';

Common Commands

-- Start a job immediately
BEGIN
  DBMS_SCHEDULER.RUN_JOB('nightly_backup_job');
END;
/
-- Pause a job
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    name  => 'nightly_backup_job',
    attribute => 'enabled',
    value  => FALSE
  );
END;
/
-- Drop a job
BEGIN
  DBMS_SCHEDULER.DROP_JOB('nightly_backup_job');
END;
/

8. Advanced Topics

a) Chained Jobs with Conditions

Use DBMS_SCHEDULER.SET_JOB_PROPERTY to add conditions:

BEGIN
  DBMS_SCHEDULER.SET_JOB_PROPERTY (
    job_name        => 'conditional_job',
    property        => 'condition',
    value           => 'SELECT COUNT(*) FROM orders WHERE status = ''pending'' > 0'
  );
END;
/

b) External Programs

Run shell scripts or batch files:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'run_script',
    job_type        => 'EXECUTABLE',
    job_action      => '/home/oracle/backup.sh',
    credentials_name => 'script_user', -- OS credentials
    enabled         => TRUE
  );
END;
/

c) Windowing

Run jobs in sliding windows (e.g., every 15 minutes):

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'monitor_every_15min',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled         => TRUE
  );
END;
/

In the Real World

1. eSewa

   • Idea Used: Event-based scheduling + job chains.
   • How: Triggers a reconciliation job when transaction logs exceed 10,000 entries. The chain includes:
     • Job 1: Lock the transaction table.
     • Job 2: Run SQL to match unmatched transactions.
     • Job 3: Unlock the table and send an email to finance.

2. NTC (Nepal Telecom)

   • Idea Used: Calendars + job classes.
   • How: Runs network latency checks every 30 minutes during business hours (9 AM–6 PM). High-priority jobs (e.g., failover scripts) use the CRITICAL_JOB_CLASS.

3. Daraz

   • Idea Used: Job chains for ETL.
   • How: Daily inventory sync follows this chain:
     • Job 1: Extract data from supplier API (runs at 2 AM).
     • Job 2: Transform data (clean duplicates, update prices).
     • Job 3: Load into Daraz’s database.
     • Job 4: Notify warehouse teams via SMS.

Exam Tip

1. Diagrams Are Key

   • Draw job chains as flowcharts (use arrows for dependencies).
   • Show calendars as time grids (e.g., "✅ 9 AM–5 PM, ❌ Weekends").

2. Syntax Matters

   • Memorize DBMS_SCHEDULER.CREATE_JOB parameters (especially repeat_interval formats like FREQ=DAILY).
   • Know the difference between job_type values (SQL_SCRIPT, PLSQL_BLOCK, EXECUTABLE).

3. Worked Examples

   • Exams often ask for job creation scripts. Practice writing:
     • A daily backup job.
     • An event-based job (e.g., "after table update").
     • A job chain for a real scenario (e.g., "bank loan processing").

4. Common Pitfalls

   • Forgetting to enable jobs: Always check enabled => TRUE.
   • Incorrect repeat_interval: Use tools like CronMaker to test.
   • Missing error handling: Add retry_count and notifications.

5. Past Exam Patterns

   • Describe: "Explain levels of locking in Oracle" → Not directly related, but job dependencies (e.g., "Job A locks Table X before Job B runs") can be linked.
   • Create: Always include enabled => TRUE and a comments field.
   • Compare: Know the difference between DBMS_JOB (legacy) and DBMS_SCHEDULER (modern).

Summary Table

In the real world

• eSewa: Uses job chains to sequence transaction reconciliation → fraud detection → admin notification, ensuring no discrepancies go unchecked during peak hours.
• NTC: Employs event-based jobs to trigger network rerouting when latency exceeds 500ms (measured via ntc_metrics table), improving service reliability.
• Daraz: Leverages calendars to run inventory sync jobs only during off-peak hours (2 AM–4 AM), reducing database load during high-traffic periods.