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 AT TIME ZONE 'America/New_York',
 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 now in a background slave to test (counts as a run;
15--    fails if a scheduled run is already in progress)
16BEGIN
17  DBMS_SCHEDULER.RUN_JOB(job_name => 'NIGHTLY_PURGE_JOB', use_current_session => FALSE);
18END;
19/
20
21-- 3. Disable, then drop the job when it is retired
22BEGIN
23  DBMS_SCHEDULER.DISABLE(name => 'NIGHTLY_PURGE_JOB', force => TRUE);
24  DBMS_SCHEDULER.DROP_JOB(job_name => 'NIGHTLY_PURGE_JOB');
25END;
26/

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. With use_current_session => FALSE, it runs in a background job slave just as a scheduled run would, which is the realistic test. Oracle notes that in this mode all relevant fields in *_SCHEDULER_JOBS are updated, so the test run shows up in the job's run count and last start date, and that RUN_JOB fails if a regularly scheduled run is in progress.
  • DISABLE then DROP_JOB — disabling first stops any new runs from starting; dropping removes the job definition entirely. force => TRUE matters here: with the default FALSE, Oracle returns an error if the job is running, while TRUE disables it and lets the running instance finish. Dropping a job also drops all argument values set for that job.

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 and no repeat_interval runs only once, at its start date. 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 calendar syntax handles month-length differences and leap years for you. Daylight saving needs one more step: the Scheduler takes the time zone from start_date, and Oracle documents that a job follows daylight-saving adjustments only when that time zone is a region name such as America/New_York. Plain SYSTIMESTAMP carries a fixed offset, which is why the example above converts it with AT TIME ZONE.

Disable before drop in scripts

In teardown scripts, call DISABLE before DROP_JOB. If a run happens to be in flight, DISABLE with the default force => FALSE raises an error; with force => TRUE it disables the job and lets the current run finish. For a job that is mid-run, DROP_JOB also accepts force => TRUE (stop the running instance, then drop) or defer => TRUE (let the running job complete, then drop).

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, jobs are not restartable, and max_failures is NULL, which means new runs of the job are started regardless of how many previous runs have failed. If you set max_failures, the job is automatically disabled and its state set to BROKEN after that many consecutive failed runs. 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 the errors column carries the error messages generated by the run (additional_info may add more detail). 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