By I still remember the first time I had to automate a nightly data purge at work. My first instinct was to write a PL/SQL script and ask a DBA to set up a cron job on the server. My senior looked at me and said “Hassan, Oracle already has a built-in job scheduler. Why are you going outside the database?”
That was the day I discovered DBMS_SCHEDULER in Oracle and honestly, it changed how I think about database automation completely.
If you are still running manual scripts, relying on OS-level schedulers, or using the old DBMS_JOB package, this DBMS_SCHEDULER Oracle tutorial is for you. I am going to walk you through everything from creating your first Oracle scheduled job to monitoring failures in production, with a first-job pattern, adaptation templates for common scenarios, and a self-contained chain demonstration. The business tables and packages named in the templates are prerequisites, not built-in Oracle objects. These corrections were checked against Oracle Database 19c documentation; they have not been executed against a database as part of this article review.
Let us get into it.
What Is DBMS_SCHEDULER in Oracle?
DBMS_SCHEDULER is Oracle’s built-in job scheduling package that lets you automate PL/SQL procedures, anonymous blocks, and external scripts directly from within the Oracle database. Introduced in Oracle 10g, it has been the recommended approach for Oracle task automation ever since.
Featured Snippet Answer: DBMS_SCHEDULER is Oracle’s built-in job scheduler for automating PL/SQL and database tasks. It lets you create Oracle scheduled jobs with flexible calendars, monitor runs using scheduler dictionary views, capture failures with error details, and orchestrate multi-step workflows using chains. It is the modern replacement for the legacy DBMS_JOB package.
DBMS_SCHEDULER vs DBMS_JOB: What Is the Difference?
This is the most searched question I see from developers coming from older Oracle environments. Here is the honest answer: DBMS_JOB is legacy and you should stop using it today.
Is DBMS_JOB deprecated in Oracle 19c? Yes. Oracle 19c automatically converts existing DBMS_JOB submissions to DBMS_SCHEDULER jobs behind the scenes. The message from Oracle is clear: DBMS_SCHEDULER is the standard for Oracle database automation in 2026.
Here is a quick comparison:
| Feature | DBMS_JOB | DBMS_SCHEDULER |
|---|---|---|
| Execution Type | PL/SQL only | PL/SQL, Executables, Shell Scripts, Chains |
| Submission transaction | SUBMIT participates in caller transaction | CREATE_JOB has different commit semantics; check each API |
| Logging | Minimal, manual | Automatic history and run details |
| Scheduling Logic | Date arithmetic (SYSDATE + 1) | Calendaring syntax (FREQ=DAILY) |
| Dependencies | None | Complex job chains with branching |
| External Access | No | Yes, via OS credentials |
DBMS_JOB.SUBMIT participates in the caller’s transaction. DBMS_SCHEDULER.CREATE_JOB is not a transactional substitute for that behavior, and Scheduler APIs have their own documented commit semantics. Prefer Scheduler for new scheduling work, but review code that depends on transactional submission before migrating it. DBMS_JOB remains supported for backward compatibility.
DBMS_SCHEDULER Oracle Architecture: Jobs, Programs, Schedules, and Chains
Before you start creating Oracle scheduled jobs, it helps to understand how DBMS_SCHEDULER is structured. Think of it as a modular system that decouples the “what”, the “when”, and the “how” of any automated task.
Jobs are the runnable things. A job points at either an inline action like a PL/SQL block or stored procedure, or a referenced program and schedule. Everything visible in DBA_SCHEDULER_JOBS is a job.
Programs are reusable definitions of “what to run” with argument definitions. If you have ten Oracle scheduled jobs that run the same procedure with different parameters, define one program and point all ten jobs at it.
Schedules define “when to run” using the repeat_interval syntax. By centralizing the schedule as its own object, you can change the timing of ten different jobs by modifying just one schedule object.
Job Classes group jobs for resource and logging governance. This is how you control CPU and IO priority across different categories of DBMS_SCHEDULER Oracle jobs.
Windows define time periods when certain resource plans or job classes are active, ensuring your heavy Oracle automation tasks do not compete with business hours workloads.
Chains are the most advanced component. They let you build multi-step workflows with dependency rules. Step B only starts if Step A succeeded. We will cover these with full code later in this article.
DBMS_SCHEDULER Create Job Example: Your First Oracle Scheduled Job
This pattern creates a daily purge job. Prerequisites: APP_MAINT is an existing schema, APP_AUDIT_LOG is an application table with a CREATED_AT DATE column, and the schema has direct CREATE PROCEDURE/CREATE JOB privileges and the required table access. Adapt these identifiers to a disposable development schema first; do not run a retention delete on production as a learning exercise.
The job time is interpreted in the time zone of start_date. Use an explicitly chosen business-region time zone if daylight-saving behavior matters, and preview upcoming times before enabling.
Always put your business logic in a stored procedure, not inline in the job. This makes your Oracle automate tasks PL/SQL code testable outside the scheduler.
-- Step 1: Create the procedure the Oracle scheduled job will call
CREATE OR REPLACE PROCEDURE app_maint.purge_old_audit_rows (
p_days_to_keep IN NUMBER DEFAULT 90
) AS
BEGIN
IF p_days_to_keep IS NULL OR p_days_to_keep < 1
OR p_days_to_keep <> TRUNC(p_days_to_keep) THEN
RAISE_APPLICATION_ERROR(-20001, 'Retention must be a positive whole number of days.');
END IF;
-- Delete audit rows older than the retention period
DELETE FROM app_audit_log
WHERE created_at < (SYSDATE - p_days_to_keep);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Roll back and re-raise so DBMS_SCHEDULER marks the run as FAILED
ROLLBACK;
RAISE;
END;
/
-- Step 2: Create the DBMS_SCHEDULER Oracle job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'APP_MAINT.PURGE_AUDIT_DAILY',
job_type => 'STORED_PROCEDURE',
job_action => 'APP_MAINT.PURGE_OLD_AUDIT_ROWS',
number_of_arguments => 1,
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
enabled => FALSE, -- Keep disabled until arguments are set
auto_drop => FALSE, -- Keep the job definition after it runs
comments => 'Daily purge of APP_AUDIT_LOG, keeps last 90 days'
);
-- Bind the argument value
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'APP_MAINT.PURGE_AUDIT_DAILY',
argument_position => 1,
argument_value => '90'
);
-- Enable only after all configuration is complete
DBMS_SCHEDULER.ENABLE('APP_MAINT.PURGE_AUDIT_DAILY');
END;
/
Key things to remember from this DBMS_SCHEDULER create job example. Keep enabled => FALSE until all arguments and configuration are set. Set auto_drop => FALSE for any operational Oracle scheduled job you need to keep running long term. Always keep job logic in a stored procedure so you can test it independently from DBMS_SCHEDULER.
8 Real-World Oracle Automate Tasks PL/SQL Examples
This is the section most Oracle developers bookmark. Here are the eight most common DBMS_SCHEDULER Oracle automation scenarios I use in real enterprise APEX and database projects.
1. Purge Old Data from Audit Tables
Already covered in the DBMS_SCHEDULER create job example above. The pattern is: delete rows older than a retention date, commit, raise on error. Essential for any high-volume Oracle database.
2. Refresh a Materialized View with DBMS_SCHEDULER Oracle
If you use materialized views for reporting, they need to stay in sync with base tables. Here is a DBMS_SCHEDULER Oracle job that does a fast refresh every two hours:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'REFRESH_SALES_MV',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_MVIEW.REFRESH(''GLOBAL_SALES_MV'', ''F''); END;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Fast refresh of GLOBAL_SALES_MV every 2 hours'
);
END;
/
The F option requests fast refresh; it only works for eligible materialized views with the required supporting configuration/logs. Verify that GLOBAL_SALES_MV exists and supports fast refresh first. C requests a complete refresh, whose duration and load should be assessed before scheduling.
3. Send Daily Email Notifications
Sending application email requires a configured mail service, permitted network access and an API suitable for its authentication/TLS requirements. UTL_MAIL, UTL_SMTP and APEX_MAIL have different setup and deployment constraints. The Oracle Wallet article explains HTTPS trust; it is not a complete SMTP configuration guide.
Once your mail server is configured, wrap UTL_MAIL.SEND in a stored procedure and schedule it as a DBMS_SCHEDULER Oracle job exactly like any other.
4. Generate Daily Executive Report Tables
For nightly dashboards or executive reports, create a DBMS_SCHEDULER Oracle job that aggregates the day’s data into a report-ready table:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'GENERATE_DAILY_SALES_REPORT',
job_type => 'STORED_PROCEDURE',
job_action => 'REPORTING_PKG.BUILD_DAILY_SUMMARY',
repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Builds daily sales summary at 11PM for next-day reporting'
);
END;
/
5. Multi-Table Data Synchronization
For environments where staging and production tables need to stay in sync, this Oracle automate tasks PL/SQL pattern keeps everything consistent:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SYNC_STAGING_TO_PROD',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN etl_pkg.merge_staging_data; END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=30;BYSECOND=0',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Incremental merge from staging to production at 1:30AM'
);
END;
/
6. Archive Transaction Logs
Transaction logs grow fast. A weekly DBMS_SCHEDULER Oracle job moves older rows to a history schema before they hurt primary database performance.
7. Nightly Batch Processing
Large calculations like interest or billing cycles should run at night when resource competition is low. Schedule these as STORED_PROCEDURE jobs pointing at your batch processing package.
8. Automated System Health Checks
My personal favourite for enterprise Oracle applications. A health check Oracle scheduled job runs hourly, queries system views for blocked sessions or low tablespace warnings, and writes anomalies to a monitoring table:
-- Prerequisites: run as APP_MAINT; use a dedicated scheduler entry point.
-- APP_HEALTH_LOG must have CHECK_TS (TIMESTAMP WITH TIME ZONE),
-- CHECK_NAME (VARCHAR2), STATUS (VARCHAR2), DETAILS (VARCHAR2(4000)).
-- A DBA must grant APP_MAINT SELECT directly on
-- SYS.DBA_TABLESPACE_USAGE_METRICS before compiling this definer-rights procedure.
CREATE OR REPLACE PROCEDURE app_maint.run_health_check AS
l_error_message VARCHAR2(4000);
BEGIN
INSERT INTO app_health_log (check_ts, check_name, status, details)
SELECT SYSTIMESTAMP,
'TABLESPACE_CHECK',
CASE
WHEN tablespace_size IS NULL OR tablespace_size = 0 THEN 'UNKNOWN'
WHEN used_space / tablespace_size * 100 > 85 THEN 'WARNING'
ELSE 'OK'
END,
tablespace_name || ' at ' ||
TO_CHAR(ROUND(used_space / NULLIF(tablespace_size, 0) * 100, 1)) || '%'
FROM sys.dba_tablespace_usage_metrics;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- SQLERRM must be captured in PL/SQL before it can be used in SQL.
l_error_message := SQLERRM;
ROLLBACK;
BEGIN
INSERT INTO app_health_log (check_ts, check_name, status, details)
VALUES (SYSTIMESTAMP, 'HEALTH_CHECK', 'FAIL', l_error_message);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- The Scheduler still receives the original error via RAISE below.
ROLLBACK;
END;
RAISE;
END;
/This is a scheduler entry point that owns its transaction. Do not call it from reusable business logic that must leave commit control to its caller. A failure to write the secondary diagnostic record must not hide the original exception: RAISE lets Scheduler record the failed run.
DBMS_SCHEDULER repeat_interval Examples: The Complete Syntax Cookbook
This is the most bookmarked and shared section in any DBMS_SCHEDULER Oracle tutorial and for good reason. The repeat_interval calendaring syntax is powerful but trips up even experienced Oracle developers. Here are your ready-to-copy DBMS_SCHEDULER repeat_interval examples:
-- Daily at 2:00 AM
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0';
-- Weekly on Monday at 6:30 AM
repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=6;BYMINUTE=30;BYSECOND=0';
-- Monthly on the 1st at 1:15 AM
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1;BYMINUTE=15;BYSECOND=0';
-- Every 10 minutes
repeat_interval => 'FREQ=MINUTELY;INTERVAL=10';
-- Every 2 hours during business days only (Monday to Friday)
repeat_interval => 'FREQ=HOURLY;INTERVAL=2;BYDAY=MON,TUE,WED,THU,FRI';
-- Every Friday at 10:00 PM
repeat_interval => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=22;BYMINUTE=0;BYSECOND=0';
-- Last Wednesday of every month
repeat_interval => 'FREQ=MONTHLY;BYDAY=-1 WED';
-- First day of each quarter
repeat_interval => 'FREQ=MONTHLY;BYMONTH=1,4,7,10;BYMONTHDAY=1';
Calendar expressions do not contain their own time zone. Set start_date using the intended region, such as a TO_TIMESTAMP_TZ value with a TZR region name, if the job should follow daylight-saving changes. SYSTIMESTAMP commonly supplies a fixed offset and is not a substitute for selecting a region.
When start_date is NULL, Oracle 19c checks a region-name session time zone first, then DEFAULT_TIMEZONE if the session uses an offset, then the SYSTIMESTAMP zone if no default is set. Verify your configuration instead of relying on an assumed zone. The weekday expression above includes every selected weekday hour; it does not restrict execution to office hours. Complex schedules can use supported calendaring features or multiple simpler jobs. Preview them with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING before enabling.
Monitoring Oracle Scheduler Jobs: The 4 Views You Need
Knowing how to create Oracle scheduled jobs is only half the job. Knowing how to monitor Oracle scheduler jobs in production is what separates a junior developer from a senior one. Here are the four dictionary views and the exact SQL queries to use.
Check all your Oracle scheduled jobs and current status:
SELECT
job_name,
enabled,
state,
last_start_date,
last_run_duration,
next_run_date,
run_count,
failure_count
FROM dba_scheduler_jobs
WHERE owner = 'APP_MAINT'
ORDER BY next_run_date;
See what DBMS_SCHEDULER Oracle jobs are running right now:
SELECT
owner,
job_name,
elapsed_time,
cpu_used
FROM dba_scheduler_running_jobs
ORDER BY elapsed_time DESC;
Why did my Oracle scheduled job fail? This is the most important monitoring query:
SELECT
job_name,
status,
error#,
actual_start_date,
run_duration,
additional_info
FROM dba_scheduler_job_run_details
WHERE owner = 'APP_MAINT'
AND job_name = 'PURGE_AUDIT_DAILY'
ORDER BY actual_start_date DESC
FETCH FIRST 20 ROWS ONLY;
Full DBMS_SCHEDULER Oracle job history and state changes:
SELECT
log_date,
owner,
job_name,
operation,
status,
additional_info
FROM dba_scheduler_job_log
WHERE owner = 'APP_MAINT'
AND job_name = 'PURGE_AUDIT_DAILY'
ORDER BY log_date DESC
FETCH FIRST 50 ROWS ONLY;
The DBA_SCHEDULER_JOB_RUN_DETAILS view can grow large on busy Oracle systems. Use DBMS_SCHEDULER.PURGE_LOG or set a global log_history attribute to manage retention automatically.
Why Is My Oracle Scheduler Job Not Running? Common DBMS_SCHEDULER Mistakes
These are the DBMS_SCHEDULER Oracle mistakes I see most often, including several I made myself early in my career.
Mistake 1: Job not enabled Creating a DBMS_SCHEDULER Oracle job does not make it active unless you set enabled => TRUE in CREATE_JOB or call DBMS_SCHEDULER.ENABLE separately. This is the single most common reason for “Oracle scheduled job not running” reports from junior developers.
Mistake 2: Unclear transaction ownership A normal successful Scheduler slave run commits at completion. Missing an explicit COMMIT is not, by itself, a reason its changes disappear. Still design transaction boundaries deliberately: a failed/stopped run and any commits already performed are different cases. Keep atomic work together, re-raise failures and avoid commits inside reusable business routines unless their contract explicitly owns the transaction.
Mistake 3: Checking the wrong monitoring view When monitoring Oracle scheduler jobs, developers often check DBA_SCHEDULER_JOBS for error messages. That view only shows current state. For actual error messages and failure details, go directly to DBA_SCHEDULER_JOB_RUN_DETAILS.
Mistake 4: Confusion about which user the DBMS_SCHEDULER Oracle job runs as The Oracle job scheduler runs the job as the owner of the job by default. Roles are ignored in definer’s rights PL/SQL blocks. Object privileges must be granted directly to the owning schema.
Mistake 5: Overcomplicating the repeat_interval When developers need irregular schedules they try to write one complex DBMS_SCHEDULER repeat_interval expression instead of just creating two simple Oracle scheduled jobs. Keep it simple and it works every time.
How to Get Email Notifications When an Oracle Scheduled Job Fails
Ask the DBA to configure the Scheduler email_server and email_sender attributes, together with the environment’s network and mail-security requirements. Then register the job-specific events with ADD_JOB_EMAIL_NOTIFICATION. SET_SCHEDULER_ATTRIBUTE configures global mail settings; it does not subscribe a recipient to a job by itself.
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION(
job_name => 'APP_MAINT.PURGE_AUDIT_DAILY',
recipients => 'operations@example.com', -- replace with the approved recipient
events => 'JOB_FAILED,JOB_BROKEN'
);
END;
/This registers outgoing notifications. Use an approved address and test delivery with a deliberately failing development job; a configured address alone does not prove mail delivery.
The max_failures attribute limits consecutive failures before the job is disabled; it is not a complete retry/backoff policy:
-- Automatically break the job after 3 consecutive failures
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'APP_MAINT.PURGE_AUDIT_DAILY',
attribute => 'max_failures',
value => 3
);
END;
/
DBMS_SCHEDULER Job Chain Example: Orchestrating Dependent Workflows
Use a DBMS_SCHEDULER Oracle chain when you need multiple steps to run in sequence with dependencies. If Step A fails, do not run Step B. This is where DBMS_SCHEDULER goes far beyond any simple cron job.
The standalone demonstration below targets a regular Oracle Database 19c installation that supports Scheduler chains. It creates its own log table, three harmless stored procedures, three programs, a chain and a one-time chain job. Do not assume this CHAIN job type is available in Autonomous Database; check the service-specific restrictions described below. Use a disposable development schema and unused OWH_ names. The schema needs CREATE TABLE, CREATE PROCEDURE, CREATE JOB and the chain-related CREATE RULE, CREATE RULE SET and CREATE EVALUATION CONTEXT privileges. Ask the DBA for those narrow grants rather than granting CREATE ANY JOB.
SUCCEEDED means the previous step completed successfully. COMPLETED also includes FAILED and STOPPED, so it is not a success-only dependency. Explicit failure/stop rules prevent a failed path from waiting indefinitely.
-- Run only in a disposable development schema.
-- Required direct privileges: CREATE TABLE, CREATE PROCEDURE, CREATE JOB,
-- CREATE RULE, CREATE RULE SET and CREATE EVALUATION CONTEXT.
-- Names below must not already exist. This demo creates a one-time job.
CREATE TABLE owh_sched_demo_log (
event_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
step_name VARCHAR2(30) NOT NULL,
logged_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);
CREATE OR REPLACE PROCEDURE owh_demo_import AS
BEGIN
INSERT INTO owh_sched_demo_log(step_name) VALUES ('IMPORT');
END;
/
CREATE OR REPLACE PROCEDURE owh_demo_validate AS
BEGIN
-- Failure test: uncomment this line, recompile, and run the job again.
-- RAISE_APPLICATION_ERROR(-20001, 'Intentional validation failure');
INSERT INTO owh_sched_demo_log(step_name) VALUES ('VALIDATE');
END;
/
CREATE OR REPLACE PROCEDURE owh_demo_cleanup AS
BEGIN
INSERT INTO owh_sched_demo_log(step_name) VALUES ('CLEANUP');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'OWH_IMPORT_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'OWH_DEMO_IMPORT',
number_of_arguments => 0,
enabled => TRUE);
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'OWH_VALIDATE_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'OWH_DEMO_VALIDATE',
number_of_arguments => 0,
enabled => TRUE);
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'OWH_CLEANUP_PROG',
program_type => 'STORED_PROCEDURE',
program_action => 'OWH_DEMO_CLEANUP',
number_of_arguments => 0,
enabled => TRUE);
DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'OWH_SALES_CHAIN');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('OWH_SALES_CHAIN', 'IMPORT', 'OWH_IMPORT_PROG');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('OWH_SALES_CHAIN', 'VALIDATE', 'OWH_VALIDATE_PROG');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('OWH_SALES_CHAIN', 'CLEANUP', 'OWH_CLEANUP_PROG');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'TRUE', 'START IMPORT');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'IMPORT SUCCEEDED', 'START VALIDATE');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'VALIDATE SUCCEEDED', 'START CLEANUP');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'CLEANUP SUCCEEDED', 'END');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'IMPORT FAILED', 'END IMPORT.error_code');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'VALIDATE FAILED', 'END VALIDATE.error_code');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN', 'CLEANUP FAILED', 'END CLEANUP.error_code');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('OWH_SALES_CHAIN',
'IMPORT STOPPED OR VALIDATE STOPPED OR CLEANUP STOPPED', 'END 20002');
DBMS_SCHEDULER.ENABLE('OWH_SALES_CHAIN');
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'OWH_SALES_CHAIN_JOB',
job_type => 'CHAIN',
job_action => 'OWH_SALES_CHAIN',
enabled => FALSE,
auto_drop => FALSE);
END;
/
-- Explicit opt-in to a single immediate run; not a recurring schedule.
BEGIN
DBMS_SCHEDULER.ENABLE('OWH_SALES_CHAIN_JOB');
END;
/
-- Query after the asynchronous run completes. Successful steps log in this order:
-- IMPORT, VALIDATE, CLEANUP.
SELECT event_id, step_name, logged_at
FROM owh_sched_demo_log
ORDER BY event_id;
SELECT job_name, job_subname, status, error#, additional_info
FROM user_scheduler_job_run_details
WHERE job_name = 'OWH_SALES_CHAIN_JOB'
ORDER BY log_date DESC;
-- To repeat after the first run, including the optional failure test:
-- BEGIN
-- DBMS_SCHEDULER.RUN_JOB('OWH_SALES_CHAIN_JOB', use_current_session => FALSE);
-- END;
-- /
After the asynchronous job finishes, the demo log should contain IMPORT, VALIDATE and CLEANUP in order, and the parent job should show SUCCEEDED. For the failure test, uncomment the intentional exception in OWH_DEMO_VALIDATE, recompile it and run the job again. That new run should be FAILED and must not add a CLEANUP row. Restore the procedure afterward. These are expected results to verify in your environment, not results claimed from a run performed for this correction.
Use ALL_SCHEDULER_RUNNING_CHAINS while a chain is running and the run-details views afterward. A chain coordinates steps; it does not make separate step jobs one atomic transaction. Successful earlier steps may already be committed when a later step fails.
DBMS_SCHEDULER Oracle in APEX and Oracle Cloud Environments
This section is specifically for Oracle APEX developers who make up a large part of the Oracle community reading this DBMS_SCHEDULER tutorial.
Oracle APEX 20.2 introduced APEX Automations, which are a developer-friendly low-code wrapper around DBMS_SCHEDULER Oracle. Here is how they compare:
| Feature | Oracle APEX Automations | Native DBMS_SCHEDULER Oracle |
|---|---|---|
| Interface | Low-code, declarative | PL/SQL code |
| Context | Full APEX session context | Standard database session |
| Execution | Coordinator polling; may run a few minutes after due time | Scheduled due times; execution may be delayed by resources |
| Best Use | APEX mail, app notifications | System maintenance, ETL |
| Portability | Exported with APEX app | Managed via SQL scripts |
Use APEX Automations when the task is tied to your application logic. Use native DBMS_SCHEDULER Oracle for anything database-level like purges, materialized view refreshes, ETL jobs, or health checks.
For getting your Oracle Cloud environment set up with APEX properly, I wrote a complete guide here: How to Set Up Oracle APEX on Oracle Cloud Free Tier
Autonomous Database has service-specific Scheduler restrictions. Its documented CREATE_JOB job types are PLSQL_BLOCK and STORED_PROCEDURE; the regular-database CHAIN demonstration above must not be assumed to run unchanged there. Predefined classes TPURGENT, TP, HIGH, MEDIUM and LOW map jobs to consumer groups. Check the official restrictions for your service and use a supported stored-procedure orchestration pattern when appropriate.
DBMS_SCHEDULER Oracle Permissions and Security
Grant the minimum privileges required by the operation and invocation context. In definer-rights stored PL/SQL, relevant privileges must be granted directly because roles are disabled. Do not generalize that restriction into a claim that roles never matter for every interactive Scheduler call.
| Privilege | What It Does |
|---|---|
| CREATE JOB | Create Oracle scheduled jobs in your own schema |
| CREATE ANY JOB | Create jobs in any schema except SYS |
| EXECUTE ON DBMS_SCHEDULER | Basic right to use the Oracle job scheduler package |
| MANAGE SCHEDULER | Administrative right to change global attributes |
The most important security rule: CREATE ANY JOB must be restricted to DBAs only. A developer with this privilege can create a DBMS_SCHEDULER Oracle job that runs in the context of a more privileged schema, which is a serious enterprise security risk.
Also: when calling DBMS_SCHEDULER from definer’s rights PL/SQL, object privileges must be granted directly. Oracle ignores role-based privileges in this context and it causes production issues that are frustrating to debug if you do not know about it.
Oracle Job Scheduler Best Practices for Enterprise Systems
These habits separate junior Oracle automation scripts from senior production systems.
Namespace your DBMS_SCHEDULER Oracle jobs by domain using the schema as a prefix: APP_MAINT.PURGE_AUDIT_DAILY, FIN_ETL.SYNC_GL_DATA, APEX_APP.SEND_DAILY_DIGEST. When you manage 50 Oracle scheduled jobs, this convention is what keeps you sane.
Always separate the program and schedule objects from the job for anything reusable. Inline definitions work for quick tests but create unmanageable environments at scale.
Set logging levels intentionally. LOGGING_RUNS is the default and sufficient for most Oracle scheduled jobs. Use LOGGING_FULL for critical financial jobs. Reduce logging for high-frequency jobs to prevent the log tables growing too large.
Put all DBMS_SCHEDULER Oracle job logic in packages, not inline blocks. A job that calls PURGE_PKG.RUN is testable, versionable, and debuggable. A job with 100 lines of inline PL/SQL is none of those things.
Mastering DBMS_SCHEDULER Oracle: The Bigger Picture
The first-job pattern and chain demonstration cover the scheduling mechanics. The reporting, ETL, archive and billing sections are adaptation patterns: their application procedures, tables and operational checks must exist before those job definitions can run. Compile and test in a development schema, inspect run details, preview the schedule, and only then enable an approved production job.
The key mindset shift is treating your Oracle scheduled jobs as first-class production assets, not background scripts. They need proper naming, proper error handling with re-raise, proper monitoring using the scheduler dictionary views, and intentional logging levels. When a DBMS_SCHEDULER Oracle job fails at 2 AM on a Sunday, DBA_SCHEDULER_JOB_RUN_DETAILS should give you the complete picture without anyone needing to dig through application logs manually.
For writing clean PL/SQL procedures that your Oracle scheduled jobs call, I covered the most common mistakes that affect background jobs here: 5 PL/SQL Mistakes I Made as a Junior Oracle Developer
If you are thinking about where DBMS_SCHEDULER fits in your broader Oracle developer career roadmap, this guide covers it: How to Start Your Oracle Developer Career in 2026
What daily task are you planning to automate first with DBMS_SCHEDULER Oracle? Drop it in the comments below. I read every single one and I would love to hear what your team is automating right now.
Hassan Raza
Oracle ACE Apprentice | SH Software Solution, Pakistan
1 Comment
Comments are closed.