Saturday, 28 June 2025

How to automate Batch Jobs with DBMS_SCHEDULER in Oracle PL/SQL ?

In this blog post, we will discuss on the DBMS_SCHEDULERs in Oracle. Here are some of the code snippets in this post.

Managing background jobs is essential for any Oracle-based system — from data processing to scheduled reports. 

Oracle’s DBMS_SCHEDULER package is a powerful tool that helps you schedule, manage, and monitor jobs efficiently. 

In this blog post, we’ll walk through how to create, schedule, and maintain batch jobs using DBMS_SCHEDULER.

What is DBMS_SCHEDULER?

DBMS_SCHEDULER is a PL/SQL package that lets you schedule and run jobs like procedures, shell scripts, and external programs. It’s more advanced than the older DBMS_JOB, with better control, logging, and flexibility.

Basic Components of a Scheduler Job

  • Program: Defines what to run (e.g., a stored procedure or script).

  • Schedule: Defines when to run.

  • Job: Links the program and schedule.

  • Job Class, Windows & Chains: Advanced features for grouping and managing jobs.

Example: Create a Simple PL/SQL Job

1. Create a Sample Procedure

CREATE OR REPLACE PROCEDURE update_status_proc IS
BEGIN
  UPDATE my_table
  SET status = 'COMPLETED'
  WHERE status = 'PENDING';

  COMMIT;
END;

2. Create a Scheduler Job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'UPDATE_STATUS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'UPDATE_STATUS_PROC',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2', -- Every day at 2 AM
    enabled         => TRUE,
    comments        => 'Daily status update batch job'
  );
END;

Monitoring the Job

Check Job Status

SELECT job_name, status, run_count, last_start_date, next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'UPDATE_STATUS_JOB';

Check Job Run History

SELECT job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'UPDATE_STATUS_JOB'
ORDER BY actual_start_date DESC;

Modifying an Existing Job

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'UPDATE_STATUS_JOB',
    attribute => 'repeat_interval',
    value     => 'FREQ=HOURLY; INTERVAL=2'); -- Every 2 hours
END;

Dropping a Job

BEGIN
  DBMS_SCHEDULER.DROP_JOB('UPDATE_STATUS_JOB');
END;

Advanced Features

Using Programs and Schedules Separately

You can define them once and reuse:


-- Define a program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'my_batch_program',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'update_status_proc',
    enabled        => TRUE
  );
END;

-- Define a schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE (
    schedule_name => 'daily_2am',
    start_date    => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    comments      => 'Runs every day at 2 AM'
  );
END;

-- Create a job using both
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name      => 'update_status_job_v2',
    program_name  => 'my_batch_program',
    schedule_name => 'daily_2am',
    enabled       => TRUE
  );
END;

Granting Permissions

If you're not using SYS or DBA user, grant privileges:

GRANT CREATE JOB TO your_user;
GRANT MANAGE SCHEDULER TO your_user;

Tips and Best Practices

  • Always log your job runs (custom or via dba_scheduler_job_log)

  • Use job classes to group similar jobs (e.g., hourly jobs, daily jobs)

  • Use disable/enable carefully when maintaining production jobs

  • Avoid committing large volumes in one go — break logic into batches

Conclusion

Oracle’s DBMS_SCHEDULER is a robust tool that enables you to automate and manage recurring tasks efficiently. Whether you’re running ETL jobs, clean-up tasks, or reports, mastering it can save hours of manual work and ensure consistency.

Posted by at