DBMS_Scheduler in Oracle SQL

Oracle DBMS_Scheduler: Detailed Explanation

The DBMS_Scheduler package is a powerful feature in Oracle Database that allows you to schedule, manage, and automate jobs in the database. It provides a more flexible and robust way of managing scheduled tasks than the older DBMS_JOB package, offering features like job chains, program and schedule management, and advanced scheduling options.

The DBMS_Scheduler package is primarily used for automating repetitive tasks such as:

Running PL/SQL blocks

Executing SQL scripts

Performing data backups or purges

Managing partitioning and database maintenance

Executing custom scripts for monitoring or housekeeping tasks

---

Components of DBMS_Scheduler

1. Jobs: A job is a task that the scheduler executes. Jobs can be scheduled to run at specific intervals or triggered by certain events. There are different types of jobs:

PL/SQL Block: A piece of PL/SQL code to be executed.

Executable: A script or command (like a shell script) that is executed by the operating system.

Stored Procedure/Function: A stored procedure or function can be executed as a job.

1. Programs: A program defines the executable action of a job. It specifies the job type, the script or block to be executed, and its arguments.

2. Schedules: A schedule defines when a job or program should run. Schedules are flexible and allow specifying time intervals, frequency, and other parameters (e.g., run every day, weekly, monthly).

3. Job Classes: A job class is a logical grouping of jobs with a set of attributes that define how jobs are executed (e.g., priority, time limits, and retry parameters).

4. Job Chains: A job chain is a sequence of jobs that are executed in a specific order, where the success or failure of one job can control the execution of subsequent jobs.

5. Windows: A window is a time period during which jobs can run. Windows are useful for grouping jobs that should run together during specific time periods.

---

How DBMS_Scheduler Works

The basic workflow involves creating:

1. A Program: Defines what the job will do.

2. A Schedule: Defines when the job will run.

3. A Job: Defines the combination of the program and schedule.

Example of Using DBMS_Scheduler

Let’s look at a step-by-step example of how to create a scheduled job using DBMS_Scheduler.

---

Step 1: Create a Program

First, we need to define the program that will be executed. This can be a PL/SQL block or a stored procedure.

Example: Creating a Program

BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'my_program',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Hello World''); END;',
enabled => FALSE, -- Set to FALSE to define the program first
comments => 'Prints a message every day'
);
END;
/

program_name: A unique name for the program.

program_type: The type of program, e.g., PLSQL_BLOCK, EXECUTABLE, etc.

program_action: The PL/SQL block or stored procedure to execute.

enabled: Whether the program is enabled right after creation. Here, it's set to FALSE so we can set up the schedule before enabling it.

comments: A description of what the program does.

---

Step 2: Create a Schedule

Now, you’ll define when the job should run using the create_schedule procedure.

Example: Creating a Schedule

BEGIN
DBMS_SCHEDULER.create_schedule(
schedule_name => 'my_daily_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=0;BYSECOND=0', -- Run daily at 10:00 AM
enabled => TRUE
);
END;
/

schedule_name: The name of the schedule.

start_date: The timestamp when the schedule should start.

repeat_interval: Defines how often the job should run. In this case, it runs daily at 10:00 AM.

enabled: Whether the schedule is enabled immediately.

---

Step 3: Create the Job

Now that we have a program and a schedule, we can create the job that links them together.

Example: Creating a Job

BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'my_daily_job',
program_name => 'my_program', -- Program defined earlier
schedule_name => 'my_daily_schedule', -- Schedule defined earlier
enabled => TRUE, -- Enables the job immediately
comments => 'Job to print Hello World every day at 10:00 AM'
);
END;
/

job_name: The unique name of the job.

program_name: The program that defines what the job will do.

schedule_name: The schedule that defines when the job will run.

enabled: Whether the job is enabled immediately after creation.

comments: A description of what the job does.

---

Step 4: Monitor and Manage Jobs

Once the job is scheduled, Oracle handles the execution. You can monitor and manage jobs through views and procedures provided by Oracle.

Check Job Status

To view the status of a scheduled job, you can query the ALL_SCHEDULER_JOBS view.

SELECT job_name, status, last_start_date, next_run_date
FROM all_scheduler_jobs
WHERE job_name = 'MY_DAILY_JOB';

job_name: The name of the job.

status: Current status (e.g., ENABLED, DISABLED, RUNNING, SUCCEEDED, FAILED).

last_start_date: The last time the job was executed.

next_run_date: The next scheduled run time.

Disable a Job

To disable a job that has been scheduled:

BEGIN
DBMS_SCHEDULER.disable('my_daily_job');
END;
/

Drop a Job

To remove a job permanently:

BEGIN
DBMS_SCHEDULER.drop_job('my_daily_job');
END;
/

---

Advanced DBMS_Scheduler Features

1. Job Chains: Job chains allow you to link multiple jobs so that one job’s outcome can trigger the next job in sequence. You can define dependencies (success, failure, or any condition) between jobs.

2. Job Priority: You can define the priority of jobs, ensuring that critical jobs are executed first if multiple jobs are scheduled at the same time.

3. Event-Based Jobs: Jobs can be triggered based on specific database events or external events. For example, you could schedule a job to run when a certain database table is modified.

4. Windows: Windows are time periods during which jobs are allowed to run. This is useful for grouping jobs together and running them within specified time frames.

---

Conclusion

DBMS_Scheduler provides a flexible and powerful way to schedule, automate, and manage jobs in Oracle databases. By using programs, schedules, and jobs, you can automate various database maintenance tasks, ensure efficient resource usage, and eliminate manual intervention. It also offers advanced features like job chains and event-based job triggers to support more complex workflows.