Oracle Scheduler - Complete Job Management Guide

Oracle Scheduler - Complete Job Management Guide

Oracle Scheduler (DBMS_SCHEDULER) is Oracle’s advanced job scheduling and automation framework, providing powerful capabilities for scheduling and managing database jobs, programs, and chains. Available from Oracle 10g onwards, it replaces the older DBMS_JOB package with enhanced features and flexibility.

🎯 Overview

What is Oracle Scheduler?

Oracle Scheduler is a comprehensive job scheduling system that allows you to:

• Schedule database jobs with flexible timing options
• Manage job dependencies through job chains
• Control resource usage with resource groups and windows
• Monitor job execution with detailed logging and notifications
• Automate maintenance tasks with predefined maintenance windows

Key Components

Core Objects

-- View scheduler objects
SELECT object_name, object_type, status
FROM dba_scheduler_objects
ORDER BY object_type, object_name;

-- Job classes
SELECT class_name, resource_consumer_group, comments
FROM dba_scheduler_classes;

-- Scheduler windows
SELECT window_name, active, window_priority, comments
FROM dba_scheduler_windows;

🔧 Basic Job Management

Creating Simple Jobs

PL/SQL Block Job

-- Create a simple PL/SQL job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'CLEANUP_TEMP_DATA',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                         DELETE FROM temp_processing
                         WHERE created_date < SYSDATE - 7;
                         COMMIT;
                       END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    auto_drop       => FALSE,
    enabled         => TRUE,
    comments        => 'Daily cleanup of temporary processing data'
  );
END;
/

-- Check job status
SELECT job_name, state, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'CLEANUP_TEMP_DATA';

Stored Procedure Job

-- Create job calling stored procedure
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'GATHER_TABLE_STATS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'DBMS_STATS.GATHER_TABLE_STATS',
    number_of_arguments => 2,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=1',
    enabled         => FALSE,
    comments        => 'Weekly statistics gathering'
  );

  -- Set job arguments
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name          => 'GATHER_TABLE_STATS',
    argument_position => 1,
    argument_value    => 'HR'
  );

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name          => 'GATHER_TABLE_STATS',
    argument_position => 2,
    argument_value    => 'EMPLOYEES'
  );

  -- Enable the job
  DBMS_SCHEDULER.ENABLE('GATHER_TABLE_STATS');
END;
/

Advanced Scheduling Options

Complex Schedule Expressions

-- Business days only (Monday-Friday)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'BUSINESS_DAY_REPORT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN generate_daily_report; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8',
    enabled         => TRUE
  );
END;
/

-- Monthly on last day
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'MONTH_END_PROCESS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN process_month_end; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=-1; BYHOUR=23',
    enabled         => TRUE
  );
END;
/

-- Quarterly reports
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'QUARTERLY_ANALYSIS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN run_quarterly_analysis; END;',
    start_date      => DATE '2024-03-31' + INTERVAL '23' HOUR,
    repeat_interval => 'FREQ=MONTHLY; INTERVAL=3; BYMONTHDAY=31',
    enabled         => TRUE
  );
END;
/

🏗️ Programs and Schedules

Creating Reusable Programs

Program Definition

-- Create reusable program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'BACKUP_PROGRAM',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                        -- Full backup procedure
                        EXECUTE IMMEDIATE ''ALTER SYSTEM CHECKPOINT'';
                        backup_database_full;
                        cleanup_old_backups(7);
                      END;',
    number_of_arguments => 0,
    enabled        => TRUE,
    comments       => 'Database backup program'
  );
END;
/

-- Create schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name   => 'NIGHTLY_SCHEDULE',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    comments        => 'Nightly maintenance schedule'
  );
END;
/

-- Create job using program and schedule
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'NIGHTLY_BACKUP_JOB',
    program_name  => 'BACKUP_PROGRAM',
    schedule_name => 'NIGHTLY_SCHEDULE',
    enabled       => TRUE,
    comments      => 'Nightly database backup'
  );
END;
/

Program with Arguments

-- Create parameterized program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'TABLESPACE_REPORT_PROGRAM',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'generate_tablespace_report',
    number_of_arguments => 2,
    enabled        => TRUE,
    comments       => 'Tablespace usage reporting'
  );

  -- Define program arguments
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name      => 'TABLESPACE_REPORT_PROGRAM',
    argument_position => 1,
    argument_name     => 'threshold_pct',
    argument_type     => 'NUMBER',
    default_value     => '85'
  );

  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name      => 'TABLESPACE_REPORT_PROGRAM',
    argument_position => 2,
    argument_name     => 'email_list',
    argument_type     => 'VARCHAR2',
    default_value     => '[email protected]'
  );
END;
/

⛓️ Job Chains and Dependencies

Creating Job Chains

Simple Linear Chain

-- Create job chain
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name => 'DATA_PROCESSING_CHAIN',
    rule_set_name => NULL,
    evaluation_interval => NULL,
    comments => 'Daily data processing workflow'
  );
END;
/

-- Define chain steps
BEGIN
  -- Step 1: Extract data
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_PROCESSING_CHAIN',
    step_name  => 'EXTRACT_DATA',
    program_name => 'EXTRACT_PROGRAM'
  );

  -- Step 2: Transform data
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_PROCESSING_CHAIN',
    step_name  => 'TRANSFORM_DATA',
    program_name => 'TRANSFORM_PROGRAM'
  );

  -- Step 3: Load data
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_PROCESSING_CHAIN',
    step_name  => 'LOAD_DATA',
    program_name => 'LOAD_PROGRAM'
  );
END;
/

-- Define chain rules
BEGIN
  -- Start with extract
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_PROCESSING_CHAIN',
    condition  => 'TRUE',
    action     => 'START EXTRACT_DATA'
  );

  -- Transform after extract completes
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_PROCESSING_CHAIN',
    condition  => 'EXTRACT_DATA COMPLETED',
    action     => 'START TRANSFORM_DATA'
  );

  -- Load after transform completes
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_PROCESSING_CHAIN',
    condition  => 'TRANSFORM_DATA COMPLETED',
    action     => 'START LOAD_DATA'
  );

  -- End chain after load
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_PROCESSING_CHAIN',
    condition  => 'LOAD_DATA COMPLETED',
    action     => 'END'
  );
END;
/

-- Enable chain
BEGIN
  DBMS_SCHEDULER.ENABLE('DATA_PROCESSING_CHAIN');
END;
/

Conditional Branching Chain

-- Complex chain with error handling
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    comments => 'Backup with validation and error handling'
  );

  -- Define steps
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    step_name  => 'PERFORM_BACKUP',
    program_name => 'BACKUP_PROGRAM'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    step_name  => 'VALIDATE_BACKUP',
    program_name => 'VALIDATE_PROGRAM'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    step_name  => 'SEND_SUCCESS_EMAIL',
    program_name => 'SUCCESS_EMAIL_PROGRAM'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    step_name  => 'SEND_FAILURE_EMAIL',
    program_name => 'FAILURE_EMAIL_PROGRAM'
  );

  -- Define rules with error handling
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    condition  => 'TRUE',
    action     => 'START PERFORM_BACKUP'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    condition  => 'PERFORM_BACKUP COMPLETED',
    action     => 'START VALIDATE_BACKUP'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    condition  => 'VALIDATE_BACKUP COMPLETED',
    action     => 'START SEND_SUCCESS_EMAIL'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    condition  => 'PERFORM_BACKUP FAILED OR VALIDATE_BACKUP FAILED',
    action     => 'START SEND_FAILURE_EMAIL'
  );

  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'BACKUP_VALIDATION_CHAIN',
    condition  => 'SEND_SUCCESS_EMAIL COMPLETED OR SEND_FAILURE_EMAIL COMPLETED',
    action     => 'END'
  );
END;
/

🎛️ Resource Management and Windows

Creating Resource Groups and Windows

Maintenance Windows

-- Create maintenance window
BEGIN
  DBMS_SCHEDULER.CREATE_WINDOW (
    window_name      => 'WEEKEND_MAINTENANCE_WINDOW',
    resource_plan    => 'MAINTENANCE_PLAN',
    schedule_name    => 'WEEKEND_SCHEDULE',
    duration         => INTERVAL '4' HOUR,
    window_priority  => 'HIGH',
    comments         => 'Weekend maintenance window'
  );
END;
/

-- Create job class for maintenance jobs
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
    job_class_name => 'MAINTENANCE_JOB_CLASS',
    resource_consumer_group => 'MAINTENANCE_GROUP',
    service => 'MAINTENANCE_SERVICE',
    comments => 'Job class for maintenance operations'
  );
END;
/

-- Create maintenance job using window
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name     => 'WEEKEND_MAINTENANCE',
    program_name => 'FULL_MAINTENANCE_PROGRAM',
    job_class    => 'MAINTENANCE_JOB_CLASS',
    start_date   => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=2',
    enabled      => TRUE
  );
END;
/

Job Prioritization

-- High priority job class
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS (
    job_class_name => 'CRITICAL_JOB_CLASS',
    resource_consumer_group => 'SYS_GROUP',
    service => 'CRITICAL_SERVICE',
    comments => 'High priority critical jobs'
  );
END;
/

-- Set job priority
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'CRITICAL_BACKUP_JOB',
    attribute => 'JOB_PRIORITY',
    value     => 1  -- Highest priority
  );
END;
/

📊 Monitoring and Management

Job Status and History

Current Job Status

-- Active jobs
SELECT job_name, state, start_date, elapsed_time, cpu_used
FROM dba_scheduler_running_jobs
ORDER BY start_date;

-- Job queue status
SELECT job_name, state, last_start_date, next_run_date, failure_count
FROM dba_scheduler_jobs
WHERE enabled = 'TRUE'
ORDER BY next_run_date;

-- Failed jobs
SELECT job_name, state, last_start_date, failure_count, comments
FROM dba_scheduler_jobs
WHERE state = 'FAILED'
ORDER BY last_start_date DESC;

Job Execution History

-- Recent job runs
SELECT job_name, status, actual_start_date, run_duration, cpu_used, error#
FROM dba_scheduler_job_run_details
WHERE actual_start_date > SYSDATE - 1
ORDER BY actual_start_date DESC;

-- Job performance trends
SELECT job_name,
       COUNT(*) as total_runs,
       COUNT(CASE WHEN status = 'SUCCEEDED' THEN 1 END) as successful_runs,
       ROUND(AVG(EXTRACT(SECOND FROM run_duration)), 2) as avg_duration_sec,
       MAX(run_duration) as max_duration
FROM dba_scheduler_job_run_details
WHERE actual_start_date > SYSDATE - 30
GROUP BY job_name
ORDER BY avg_duration_sec DESC;

Chain Monitoring

-- Chain execution status
SELECT chain_name, state, start_date, elapsed_time
FROM dba_scheduler_running_chains;

-- Chain step details
SELECT chain_name, step_name, state, start_date, end_date, error_code
FROM dba_scheduler_chain_steps
WHERE chain_name = 'DATA_PROCESSING_CHAIN'
ORDER BY start_date;

-- Chain rules
SELECT chain_name, condition, action, rule_name
FROM dba_scheduler_chain_rules
WHERE chain_name = 'DATA_PROCESSING_CHAIN'
ORDER BY rule_name;

🔧 Job Control Operations

Starting, Stopping, and Modifying Jobs

Job Control

-- Run job immediately
BEGIN
  DBMS_SCHEDULER.RUN_JOB('CLEANUP_TEMP_DATA');
END;
/

-- Stop running job
BEGIN
  DBMS_SCHEDULER.STOP_JOB('LONG_RUNNING_JOB', force => TRUE);
END;
/

-- Disable job
BEGIN
  DBMS_SCHEDULER.DISABLE('PROBLEMATIC_JOB');
END;
/

-- Enable job
BEGIN
  DBMS_SCHEDULER.ENABLE('FIXED_JOB');
END;
/

Modifying Job Attributes

-- Change job schedule
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'CLEANUP_TEMP_DATA',
    attribute => 'REPEAT_INTERVAL',
    value     => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=30'
  );
END;
/

-- Change job action
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'MAINTENANCE_JOB',
    attribute => 'JOB_ACTION',
    value     => 'BEGIN enhanced_maintenance_procedure; END;'
  );
END;
/

-- Set job arguments
BEGIN
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name          => 'STATS_GATHERING_JOB',
    argument_position => 1,
    argument_value    => 'SALES'
  );
END;
/

📧 Notifications and Logging

Setting Up Notifications

Email Notifications

-- Create notification
BEGIN
  DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    job_name => 'CRITICAL_BACKUP_JOB',
    recipients => '[email protected]',
    subject => 'Backup Job Status: %job_name%',
    body => 'Job %job_name% finished with status %job_status% at %job_start_time%',
    events => 'JOB_FAILED,JOB_COMPLETED'
  );
END;
/

-- Configure SMTP settings (as SYSDBA)
BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'EMAIL_SERVER',
    value => 'smtp.company.com'
  );

  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'EMAIL_SENDER',
    value => '[email protected]'
  );
END;
/

Custom Logging

-- Enable detailed logging
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'DATA_PROCESSING_CHAIN',
    attribute => 'LOGGING_LEVEL',
    value     => DBMS_SCHEDULER.LOGGING_FULL
  );
END;
/

-- View job logs
SELECT job_name, log_date, operation, status, additional_info
FROM dba_scheduler_job_log
WHERE job_name = 'CRITICAL_BACKUP_JOB'
  AND log_date > SYSDATE - 7
ORDER BY log_date DESC;

🛡️ Security and Privileges

Scheduler Privileges

Required Privileges

-- Grant scheduler privileges
GRANT CREATE JOB TO app_user;
GRANT CREATE SCHEDULE TO app_user;
GRANT CREATE PROGRAM TO app_user;
GRANT EXECUTE ON DBMS_SCHEDULER TO app_user;

-- Advanced privileges
GRANT MANAGE SCHEDULER TO senior_dba;
GRANT CREATE ANY JOB TO senior_dba;
GRANT ALTER ANY JOB TO senior_dba;
GRANT DROP ANY JOB TO senior_dba;

-- Query privilege usage
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE privilege LIKE '%SCHEDULER%'
   OR privilege LIKE '%JOB%'
ORDER BY grantee, privilege;

Secure Job Design

-- Create job with credential
BEGIN
  DBMS_SCHEDULER.CREATE_CREDENTIAL (
    credential_name => 'BACKUP_CREDENTIAL',
    username => 'backup_user',
    password => 'secure_password',
    comments => 'Credential for backup operations'
  );

  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'SECURE_BACKUP_JOB',
    job_type => 'EXECUTABLE',
    job_action => '/opt/oracle/scripts/backup.sh',
    credential_name => 'BACKUP_CREDENTIAL',
    enabled => TRUE
  );
END;
/

📚 Best Practices

Design Principles

1. Use Descriptive Names

   • Job names should clearly indicate purpose
   • Include environment prefixes (DEV_, PROD_)
   • Use consistent naming conventions

2. Error Handling

   • Always implement proper error handling
   • Set up appropriate notifications
   • Use job chains for complex workflows

3. Resource Management

   • Use job classes to control resource usage
   • Schedule intensive jobs during maintenance windows
   • Monitor job performance regularly

4. Security

   • Follow principle of least privilege
   • Use credentials for external executables
   • Regularly audit job ownership and permissions

Common Patterns

Daily Maintenance Pattern

-- Template for daily maintenance jobs
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'DAILY_MAINTENANCE_' || TO_CHAR(SYSDATE, 'YYYYMMDD'),
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                         -- Update statistics
                         DBMS_STATS.GATHER_DATABASE_STATS;
                         -- Cleanup logs
                         cleanup_application_logs(7);
                         -- Optimize indexes
                         rebuild_fragmented_indexes(20);
                       END;',
    start_date      => TRUNC(SYSDATE) + 1 + INTERVAL '2' HOUR,
    auto_drop       => TRUE,
    enabled         => TRUE
  );
END;
/

🚀 Advanced Features

Event-Based Scheduling

-- Create event-based job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'FILE_ARRIVAL_JOB',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN process_incoming_file; END;',
    start_date => SYSTIMESTAMP,
    event_condition => 'tab.user_data.object_name = ''INCOMING_FILES''',
    queue_spec => 'FILE_QUEUE',
    enabled    => TRUE
  );
END;
/

Remote Jobs

-- Create remote database job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'REMOTE_STATS_JOB',
    job_type => 'STORED_PROCEDURE',
    job_action => 'DBMS_STATS.GATHER_SCHEMA_STATS',
    number_of_arguments => 1,
    destination => 'REMOTE_DB_LINK',
    enabled => TRUE
  );

  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
    job_name => 'REMOTE_STATS_JOB',
    argument_position => 1,
    argument_value => 'SALES'
  );
END;
/

Oracle Scheduler provides enterprise-grade job scheduling capabilities that go far beyond simple task automation. With proper design and implementation, it becomes a powerful tool for database automation, workflow management, and operational efficiency.

📖 Related Topics

• RMAN Backup Automation - Automate backup procedures
• AWR Report Generation - Schedule performance reports
• Data Pump Automation - Automate data exports
• Database Maintenance - Comprehensive maintenance strategies