How to Schedule Jobs in Oracle

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.