How to Schedule Jobs in Oracle

Galaxy Glossary

How do I schedule jobs in Oracle using DBMS_SCHEDULER?

DBMS_SCHEDULER lets you run PL/SQL, SQL, or OS scripts automatically at set times or intervals.

Welcome to Galaxy!
You'll be receiving a confirmation email.

In the meantime, follow us on Twitter

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is DBMS_SCHEDULER used for?

DBMS_SCHEDULER automates routine tasks—backups, data clean-ups, email alerts—by running PL/SQL, SQL scripts, or external programs without manual intervention. It supersedes the older DBMS_JOB package and offers finer control, calendar-based scheduling, and detailed logging.

How do I create a one-time job?

Call DBMS_SCHEDULER.CREATE_JOB with schedule_date. The job executes once at the specified timestamp and then completes.

Example one-time job

The block archives shipped orders into a history table.

BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'archive_orders_once', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN INSERT INTO orders_history ...; END;', schedule_date => SYSTIMESTAMP + INTERVAL '1' DAY, enabled => TRUE); END; /

How do I create a recurring job?

Use repeat_interval with a calendaring expression or a simple frequency. This job recalculates inventory totals every night.

BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'refresh_stock_daily', job_type => 'STORED_PROCEDURE', job_action => 'pkg_inventory.refresh_stock_levels', repeat_interval => 'FREQ=DAILY; BYHOUR=02; BYMINUTE=0', enabled => TRUE); END; /

How can I monitor and manage jobs?

Query DBA_SCHEDULER_JOBS for definitions and DBA_SCHEDULER_JOB_RUN_DETAILS for run history. Use DBMS_SCHEDULER.ENABLE, DISABLE, RUN_JOB, or DROP_JOB to control execution.

Best practices for DBMS_SCHEDULER

Store job logic in packaged procedures, keep transactions short, log errors with raise_application_error, and set auto_drop => FALSE when you need persistent logs.

Common errors and troubleshooting tips

ORA-27475 indicates a wrong calendar string; validate with DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. ORA-27365 often means the job owner lacks CREATE JOB privilege—grant it then retry.

FAQs

How do I change a job schedule without recreating it?

Call DBMS_SCHEDULER.SET_ATTRIBUTE for repeat_interval, then enable the job again.

Can I run OS scripts?

Yes—define a credential, set job_type => 'EXECUTABLE', and point job_action to the shell script path.

What views show job failures?

Check DBA_SCHEDULER_JOB_RUN_DETAILS and DBA_SCHEDULER_JOB_LOG for error codes and stack traces.

Why How to Schedule Jobs in Oracle is important

How to Schedule Jobs in Oracle Example Usage


BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'email_new_customers',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'pkg_marketing.send_welcome_email',
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    start_date      => SYSTIMESTAMP,
    enabled         => TRUE,
    comments        => 'Send welcome emails to new entries in Customers table');
END;
/

How to Schedule Jobs in Oracle Syntax


BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name          => 'job_name',                -- VARCHAR2 up to 128
    job_type          => 'PLSQL_BLOCK' | 'STORED_PROCEDURE' | 'EXECUTABLE',
    job_action        => 'code_or_program',         -- Procedure name or PL/SQL block
    number_of_arguments=> 0,                        -- For programs with arguments
    start_date        => TIMESTAMP WITH TIME ZONE,  -- When to begin
    repeat_interval   => 'FREQ=DAILY;BYHOUR=2',     -- Calendar string (NULL for one-time)
    end_date          => NULL,                      -- Optional stop time
    job_class         => 'DEFAULT_JOB_CLASS',
    auto_drop         => FALSE | TRUE,              -- Keep definition after completion
    enabled           => TRUE | FALSE,
    comments          => 'Description');
END;
/

Common Mistakes

Using DBMS_JOB instead of DBMS_SCHEDULER—this limits calendar options. Migrate by recreating jobs with DBMS_SCHEDULER for better control and logging.
Enabling a job before granting CREATE JOB to the owner, leading to ORA-27365. Fix by granting the privilege first, then enable the job.