This article reveals the secrets of Oracle Job Scheduler, from basic concepts to advanced features, with practical examples to help you master this essential tool.

Introduction to Oracle Job Scheduler

Job Scheduler is a powerful Oracle tool that lets you automate repetitive tasks, such as running stored procedures, transferring data, and maintaining the database. With Job Scheduler, you can ensure that your tasks run on time without manual intervention.

What Is Job Scheduler?

Oracle Job Scheduler is a feature that lets you schedule and execute tasks automatically at regular intervals or at specific times. This is particularly useful for maintenance and administration tasks that need to run outside peak hours.

Basic Job Scheduler Configuration

Let us start with the basics: how to create and schedule a simple job in Oracle.

Creating a Basic Job

First, we need to create a stored procedure, which we have discussed previously in this article, for our job to execute. Suppose we have a procedure called backup_database that creates a backup of our database.

CREATE OR REPLACE PROCEDURE backup_database IS
BEGIN
    -- Logic for backing up the database
    NULL;
END;
/

Now we create a job to run this procedure every day at midnight.

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

Job Scheduler Parameters

  • job_name: Unique name for the job.

  • job_type: Type of job (PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE).

  • job_action: The action the job will perform, such as a PL/SQL block or a stored procedure call.

  • start_date: The date and time when the job starts.

  • repeat_interval: The interval at which the job repeats.

  • enabled: Whether the job is enabled.

Advanced Job Scheduler Examples

Once you understand the basics, you can explore more advanced Job Scheduler features.

Jobs with Conditions and Dependencies

We can create jobs that run only when certain conditions are met or after other jobs have completed successfully. Suppose we want backup_database to run only after a previous job called data_cleanup_job has completed.

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'DATA_CLEANUP_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN cleanup_data; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE
    );

    DBMS_SCHEDULER.create_job (
        job_name        => 'CONDITIONAL_BACKUP_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN backup_database; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        job_class       => 'DATA_CLEANUP_CLASS'
    );

    DBMS_SCHEDULER.create_chain (
        chain_name      => 'BACKUP_CHAIN',
        rule_set_name   => NULL,
        evaluation_interval => NULL
    );

    DBMS_SCHEDULER.define_chain_step (
        chain_name      => 'BACKUP_CHAIN',
        step_name       => 'STEP1',
        program_name    => 'DATA_CLEANUP_JOB'
    );

    DBMS_SCHEDULER.define_chain_step (
        chain_name      => 'BACKUP_CHAIN',
        step_name       => 'STEP2',
        program_name    => 'CONDITIONAL_BACKUP_JOB'
    );

    DBMS_SCHEDULER.define_chain_rule (
        chain_name      => 'BACKUP_CHAIN',
        condition       => 'STEP1 COMPLETED',
        action          => 'START STEP2'
    );

    DBMS_SCHEDULER.enable (name => 'BACKUP_CHAIN');
END;
/

Monitoring and Error Handling

Monitoring jobs and handling errors is essential to ensure that tasks run correctly.

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

You can configure notifications to receive alerts when errors occur:

BEGIN
    DBMS_SCHEDULER.set_attribute (
        name      => 'DAILY_BACKUP_JOB',
        attribute => 'raise_events',
        value     => DBMS_SCHEDULER.event_job_failed
    );
END;
/

BEGIN
    DBMS_SCHEDULER.add_job_email_notification (
        job_name            => 'DAILY_BACKUP_JOB',
        recipients          => '[email protected]',
        subject             => 'Job Failure Alert',
        body                => 'The job DAILY_BACKUP_JOB has failed. Please check the logs for more details.',
        events              => DBMS_SCHEDULER.event_job_failed
    );
END;
/

Conclusion

Oracle Job Scheduler is a powerful tool for automating database tasks, from basic jobs to advanced configurations with dependencies and error handling. With this guide, you now have the knowledge needed to implement and manage jobs efficiently, keeping your Oracle database running and maintained properly.

Try these examples and discover how Job Scheduler can simplify and optimize your daily tasks.