π§ 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)