How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB

The Oracle Scheduler runs PL/SQL, stored procedures, and programs on a schedule, inside the database. DBMS_SCHEDULER.CREATE_JOB defines a job in one call: what to run, when to start, and how often to repeat. RUN_JOB runs it on demand, STOP_JOB stops a running job, and DROP_JOB removes it.

Code for This Guide

The main examples are in the examples/pkg-scheduler folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_scheduler.create_job(
  job_name        => 'NAME',
  job_type        => 'PLSQL_BLOCK',        -- or STORED_PROCEDURE, EXECUTABLE, CHAIN
  job_action      => 'begin ... end;',
  start_date      => timestamp_with_time_zone,
  repeat_interval => 'calendar expression',
  enabled         => true,
  comments        => '...');

Without a REPEAT_INTERVAL the job runs once. Without a START_DATE it starts as soon as it is enabled.

The examples log to a small table:

Setup:

create table job_log (logged_at timestamp default systimestamp,
                      job_name varchar2(30), message varchar2(200));

Create a Nightly Job

Example:

begin
  dbms_scheduler.create_job(
    job_name        => 'NIGHTLY_STATUS_CLEANUP',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[begin
                            insert into job_log (job_name, message)
                            select 'NIGHTLY_STATUS_CLEANUP',
                                   count(*) || ' flights scheduled'
                            from   flights where status = 'SCHEDULED';
                            commit;
                          end;]',
    start_date      => timestamp '2027-01-01 02:30:00 Asia/Dubai',
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=30',
    enabled         => true,
    comments        => 'Counts the flights still scheduled, nightly');
end;
/
select job_name, state, next_run_date from user_scheduler_jobs;

Output:

PL/SQL procedure successfully completed.

JOB_NAME                  STATE        NEXT_RUN_DATE
_________________________ ____________ __________________________________
NIGHTLY_STATUS_CLEANUP    SCHEDULED    01-JAN-2027 02:30:00 ASIA/DUBAI

The job counts the flights still scheduled at 02:30 Dubai time every night, starting on 1 January 2027. USER_SCHEDULER_JOBS shows it SCHEDULED, with the next run date in the job's time zone.

Run It Now

Example:

begin
  dbms_scheduler.run_job('NIGHTLY_STATUS_CLEANUP', use_current_session => true);
end;
/
select job_name, message from job_log;

select job_name, status, error#, run_duration
from   user_scheduler_job_run_details
where  job_name = 'NIGHTLY_STATUS_CLEANUP'
and    log_date > systimestamp - interval '1' minute;           -- this run

Output:

PL/SQL procedure successfully completed.

JOB_NAME                  MESSAGE
_________________________ ________________________
NIGHTLY_STATUS_CLEANUP    538 flights scheduled

JOB_NAME                  STATUS          ERROR# RUN_DURATION
_________________________ ____________ _________ ______________________
NIGHTLY_STATUS_CLEANUP    SUCCEEDED            0 +00 00:00:00.000000

RUN_JOB with use_current_session TRUE runs the job immediately in your session and waits for it, which is handy for testing. The job wrote its count to JOB_LOG, and the run is recorded in USER_SCHEDULER_JOB_RUN_DETAILS as SUCCEEDED.

Stop and Drop Jobs

Example:

begin
  dbms_scheduler.create_job(job_name   => 'LONG_JOB', job_type => 'PLSQL_BLOCK',
                            job_action => 'begin dbms_session.sleep(60); end;',
                            enabled    => true, auto_drop => false);
end;
/
exec dbms_session.sleep(3)

select job_name from user_scheduler_running_jobs;

begin
  dbms_scheduler.stop_job('LONG_JOB');
  dbms_scheduler.drop_job('LONG_JOB');
  dbms_scheduler.drop_job('NO_SUCH_JOB');
end;
/
select job_name, status from user_scheduler_job_run_details
where  job_name = 'LONG_JOB' and log_date > systimestamp - interval '1' minute;

Output:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

JOB_NAME
___________
LONG_JOB

begin
*
ERROR at line 1:
ORA-27475: unknown job "NIMBUS"."NO_SUCH_JOB"
ORA-06512: at "SYS.DBMS_ISCHED", line 302
ORA-06512: at "SYS.DBMS_SCHEDULER", line 843
ORA-06512: at line 4

JOB_NAME    STATUS
___________ __________
LONG_JOB    STOPPED

LONG_JOB starts at once and shows in USER_SCHEDULER_RUNNING_JOBS. STOP_JOB ends it, and its run is logged as STOPPED. Dropping a job that does not exist fails with ORA-27475.

Things to Know

• Give START_DATE a time zone region, such as Asia/Dubai, so daylight saving time changes do not shift the run time.
• Creating jobs needs the CREATE JOB privilege, which DB_DEVELOPER_ROLE includes.
• One-time jobs are dropped after they run unless AUTO_DROP is FALSE.

When you are done, remove the job and the table:

Cleanup:

exec dbms_scheduler.drop_job('NIGHTLY_STATUS_CLEANUP')
drop table job_log purge;

Related Guides

• How to Run AI Jobs in the Background with DBMS_SCHEDULER
• How to Store Credentials with DBMS_CREDENTIAL

Conclusion

DBMS_SCHEDULER.CREATE_JOB defines what to run and when in one call. Test jobs with RUN_JOB in your session, check their runs in USER_SCHEDULER_JOB_RUN_DETAILS, and stop or drop them with STOP_JOB and DROP_JOB.