Oracle DBMS_SCHEDULER: Create and Manage Jobs with PL/SQL

calendar Jun 8, 2026 · 7 min read · Oracle Scheduler DBMS_SCHEDULER CREATE_JOB PL/SQL Job Scheduling Oracle DBA Scripts  ·
Share on: linkedin copy

Oracle DBMS_SCHEDULER: Create and Manage Jobs with PL/SQL

Purpose

Automating a recurring task — a nightly statistics gather, a log purge, an end-of-month rollup — is a standard part of running an Oracle database. DBMS_SCHEDULER.CREATE_JOB is the procedure that registers that task with the database so it runs on a calendar schedule without an external cron entry. The job, its action, and its schedule all live inside the database, which means they are backed up, replicated to a standby, and visible in the data dictionary.

This post shows the full lifecycle: create a job with a PL/SQL action and a calendar repeat interval, enable it, run it once on demand to test, and drop it cleanly when it is no longer needed. Each step is a single procedure call, and every job created this way is then visible in DBA_SCHEDULER_JOBS and, while running, in DBA_SCHEDULER_RUNNING_JOBS.

Keeping the schedule inside the database is the main reason to prefer the Scheduler over operating-system cron: there is one place to look, one place to back up, and one security model.

Code

 1-- 1. Create a job that runs a PL/SQL block every night at 02:00
 2BEGIN
 3  DBMS_SCHEDULER.CREATE_JOB (
 4    job_name        => 'NIGHTLY_PURGE_JOB',
 5    job_type        => 'PLSQL_BLOCK',
 6    job_action      => 'BEGIN purge_old_audit_rows(30); END;',
 7    start_date      => SYSTIMESTAMP,
 8    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
 9    enabled         => TRUE,
10    comments        => 'Purge audit rows older than 30 days');
11END;
12/
13
14-- 2. Run it once immediately to test (does not affect the schedule)
15BEGIN
16  DBMS_SCHEDULER.RUN_JOB(job_name => 'NIGHTLY_PURGE_JOB', use_current_session => FALSE);
17END;
18/
19
20-- 3. Disable, then drop the job when it is retired
21BEGIN
22  DBMS_SCHEDULER.DISABLE(name => 'NIGHTLY_PURGE_JOB');
23  DBMS_SCHEDULER.DROP_JOB(job_name => 'NIGHTLY_PURGE_JOB');
24END;
25/

Code Breakdown

Creating the job

  • job_name — a unique name in the schema namespace. It becomes the handle used by every later call (RUN_JOB, DISABLE, DROP_JOB) and the value you see in DBA_SCHEDULER_JOBS.
  • job_type => 'PLSQL_BLOCK' — tells the Scheduler the action is an anonymous PL/SQL block. Other common types are STORED_PROCEDURE (call a named procedure) and EXECUTABLE (run an external OS program).
  • job_action — the actual code to run. For a PLSQL_BLOCK it is the text of the block; for a STORED_PROCEDURE it is the procedure name.
  • start_date => SYSTIMESTAMP — the earliest time the job may run. Using the current timestamp means the schedule begins immediately.
  • repeat_interval — the calendar expression that defines recurrence. FREQ=DAILY; BYHOUR=2 means "every day at 02:00." This calendaring syntax is far more readable than a cron string and supports rules cron cannot express, such as FREQ=MONTHLY; BYDAY=-1FRI (the last Friday of each month).
  • enabled => TRUE — creates the job in an enabled state so it starts running on schedule. Pass FALSE to stage a job and enable it later.

Testing and retiring the job

  • RUN_JOB — fires the job once, on demand, independent of its schedule. With use_current_session => FALSE, it runs in a background job slave just as a scheduled run would, which is the realistic test.
  • DISABLE then DROP_JOB — disabling first stops any new runs from starting; dropping removes the job definition entirely. Dropping a job also removes its entries from the Scheduler views.

Key Points

  • The job action runs as the job's owner, with that schema's privileges. A PLSQL_BLOCK cannot rely on privileges granted only through a role unless the role is active for the definer.
  • repeat_interval uses Oracle's calendaring syntax, not a cron string. The grammar is FREQ=...; INTERVAL=...; BYxxx=.... Omitting repeat_interval creates a one-time job that runs once at start_date and then completes.
  • A job created with enabled => TRUE but no valid repeat_interval runs exactly once. This is the correct pattern for a fire-and-forget background task.
  • Every run is logged. DBA_SCHEDULER_JOB_RUN_DETAILS records status, actual start, run duration, and any error for each execution — the audit trail cron does not give you.
  • In a multitenant (CDB) database, jobs are local to the pluggable database (PDB) in which they are created. Create the job while connected to the correct PDB.

Insights and Best Practices

Prefer stored procedures over inline blocks

For anything beyond a one-line action, set job_type => 'STORED_PROCEDURE' and point job_action at a named, version-controlled procedure. Inline PLSQL_BLOCK text is hard to review and easy to break with a stray quote. A stored procedure is testable on its own and lives in source control.

Test with RUN_JOB before trusting the schedule

RUN_JOB with use_current_session => FALSE reproduces the real execution context — the job slave, the owner's privileges, the autonomous transaction boundary. Running the PL/SQL by hand in your own session does not, because your session may have privileges or settings the job slave lacks. Always do the on-demand background run before walking away.

Use calendar expressions, not arithmetic

Write FREQ=WEEKLY; BYDAY=SAT; BYHOUR=23 rather than computing the next Saturday in PL/SQL. The Scheduler handles daylight-saving transitions, month-length differences, and leap years for you when you express the schedule declaratively.

Disable before drop in scripts

In teardown scripts, call DISABLE before DROP_JOB. If a run happens to be in flight, disabling stops the next one cleanly; dropping a job mid-run can leave the current execution to finish while the definition disappears, which is confusing to anyone watching the views.

Separate the schedule from the action with programs and schedules

For a job you will reuse or vary, consider splitting it into a named program (the action) and a named schedule (the calendar), then binding them in the job. CREATE_PROGRAM and CREATE_SCHEDULE let several jobs share one schedule, and let you change a schedule in one place rather than editing every job that uses it. The inline CREATE_JOB shown above is the right starting point for a single task; the program-and-schedule split pays off once you have a family of related jobs that should all run on the same calendar.

Set realistic failure handling

By default a job that raises an error is retried according to its max_failures and the Scheduler's restart logic, and after repeated failures it is marked broken and stops. Decide deliberately what should happen when your action fails: a transient network job might warrant retries, while a data-integrity job should fail loudly and stay failed so a human looks at it. Setting these attributes explicitly is better than discovering the defaults during an incident.

When to Use This

  • Scheduling recurring maintenance: statistics gathering, partition rollover, audit purges.
  • Replacing operating-system cron entries with in-database scheduling for backup and replication coverage.
  • Running a one-time background task after a deployment without holding your session open.
  • Building a series of dependent jobs where one job's completion triggers the next.

Troubleshooting Common Issues

If a job never runs, first check DBA_SCHEDULER_JOBS.enabled and state — a job left disabled, or one that hit the broken-job retry limit, will sit idle. If a job runs but fails, read DBA_SCHEDULER_JOB_RUN_DETAILS for that job: the status column shows FAILED, and additional_info carries the error stack. A common cause is a missing privilege, because the action runs as the job owner, not as the DBA who created it. If the schedule seems off by hours, confirm the database time zone and the job's start_date time zone match your expectation, since calendar expressions are evaluated in the job's own time zone.

References

Posts in this series