DEV Community

Pranav Bakare
Pranav Bakare

Posted on

DBMS_SCHEDULER in ORACLE Database

🧠 What is DBMS_SCHEDULER?

DBMS_SCHEDULER is a built-in Oracle PL/SQL package that allows you to create, schedule, manage, and monitor jobs to run in the Oracle database environment.

Think of it as Oracle's internal job scheduler β€” a powerful alternative to OS-level tools like cron.


βœ… Why/When Do You Use It?

Automate routine database tasks like:

Running PL/SQL procedures or anonymous blocks

Moving/archiving data

Sending email alerts

File processing (via UTL_FILE)

Schedule ETL jobs (e.g., data load from external tables)

Refresh materialized views

Generate reports at specific intervals

Perform health checks or backups within the database


πŸ”§ Core Components of DBMS_SCHEDULER

Component Purpose

Job The actual task you want to run (e.g., a procedure or SQL block).
Program Defines what action to take (e.g., which procedure or command to run).
Schedule Defines when and how often the job runs (e.g., daily at 5 AM).
Job Class Groups jobs for resource allocation or prioritization.
Window / Window Group Time periods during which resources can be allocated (used for resource plans).
Chain Allows defining a workflow of multiple steps with conditions and dependencies.


πŸ§ͺ Example: Basic Job (No Program or Schedule)

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'job_hello_world',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Hello World!''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=5',
enabled => TRUE
);
END;
/


πŸ“Œ Equivalent with Program & Schedule Separation

-- Step 1: Create program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'prog_hello',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN my_procedure; END;',
enabled => TRUE
);
END;
/

-- Step 2: Create schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'daily_5am',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=5; BYMINUTE=0',
enabled => TRUE
);
END;
/

-- Step 3: Create job using program and schedule
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'job_my_proc',
program_name => 'prog_hello',
schedule_name => 'daily_5am',
enabled => TRUE
);
END;
/


πŸ†š DBMS_SCHEDULER vs CRON

Feature DBMS_SCHEDULER (Oracle) Cron (OS-Level)

Level Runs inside Oracle DB Runs in OS shell
Language PL/SQL, SQL Shell scripts
DB Credentials Uses Oracle's internal engine Requires user/password if accessing DB
Monitoring Logs available in data dictionary (SCHEDULER views) Basic logs unless custom logging added
Dependencies Can define chains, failures, retries Needs external scripting
Security Fully controlled by Oracle Limited to OS permissions
Portability Portable across Oracle systems Tied to OS environment


🎯 When to Use DBMS_SCHEDULER over Cron?

Use DBMS_SCHEDULER when:

Task is database-centric (runs SQL/PLSQL)

You want DB-level monitoring/auditing/logging

You need fine-grained job control (failures, retry, dependencies)

You want to avoid external OS script dependencies

You're working in a managed cloud DB (e.g., Oracle Autonomous DB where OS-level access isn’t allowed)


πŸ” Useful Views for Monitoring

View Description

ALL_SCHEDULER_JOBS All jobs visible to current user
DBA_SCHEDULER_JOBS All jobs in the database
USER_SCHEDULER_JOBS Jobs owned by the current user
*_SCHEDULER_JOB_RUN_DETAILS History of job runs
*_SCHEDULER_JOB_LOG Logs and status of job executions


βœ… Final Thought

Oracle's DBMS_SCHEDULER is much more powerful than CRON for database-level task automation, especially when it comes to complex workflows, built-in logging, chaining, and tight security. It is a best practice for production environments where jobs interact heavily with Oracle data.

Top comments (0)