As a software developer, you often write code that must run reliably in the background: data cleanup, periodic recalculations, imports, exports, and batch processing. Pushing this responsibility to external scripts or application-level schedulers is one way to do it, but it can add complexity and increase failure points.
Oracle’s job scheduling feature lets you move this logic into the database, close to the data it operates on, using plain SQL and PL/SQL. The result is simpler code, fewer moving parts, and background tasks that run predictably without constant supervision.
The Scheduler
This feature is provided by the DBMS_SCHEDULER package.
A basic example is a daily cleanup task. Suppose you have a table called ORDERS and you want to remove records older than five years every night. First, you create a stored procedure that performs the cleanup.
CREATE OR REPLACE PROCEDURE cleanup_old_orders IS
BEGIN
DELETE FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -60);
COMMIT;
END;
/
Next, you create a scheduler job that runs this procedure every day at 2 a.m.:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'cleanup_old_orders_job',
job_type => 'STORED_PROCEDURE',
job_action => 'CLEANUP_OLD_ORDERS',
start_date => TIMESTAMP '2026-01-01 02:00:00',
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
enabled => TRUE
);
END;
/
Once enabled, Oracle runs this job automatically every night.
Another common example is running a job at short, repeating intervals. For instance, you may want to refresh a summary table every 10 minutes:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'refresh_sales_summary_job',
job_type => 'PLSQL_BLOCK',
job_action => '
BEGIN
refresh_sales_summary;
END;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
enabled => TRUE
);
END;
/
The scheduler can also be used to run jobs only once. For example, you might need to perform a one-time data fix during a maintenance window:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'one_time_data_fix_job',
job_type => 'PLSQL_BLOCK',
job_action => '
BEGIN
update_customer_status;
END;',
start_date => TIMESTAMP '2026-03-15 23:00:00',
enabled => TRUE
);
END;
/
Required privileges
In addition to defining jobs, it is important to understand the required privileges. To create and manage scheduler jobs, a user needs the CREATE JOB privilege. To create jobs in another schema, CREATE ANY JOB is required. Running jobs that use DBMS_SCHEDULER also requires EXECUTE privilege on the DBMS_SCHEDULER package.
If a job runs a stored procedure, the job owner must have direct privileges on the objects used by that procedure, not privileges granted through roles. For example, if a job deletes rows from the ORDERS table, the job owner must have a direct DELETE grant on that table. For external jobs, additional privileges such as CREATE EXTERNAL JOB and specific operating system credentials are required.
These rules ensure that jobs run securely and only perform actions explicitly allowed by the database administrator.
Monitoring
For monitoring, Oracle DB stores job execution details in system views. You can check whether a job is enabled and when it last ran with a simple query:
SELECT job_name, enabled, last_start_date, last_run_duration
FROM dba_scheduler_jobs
WHERE job_name = 'CLEANUP_OLD_ORDERS_JOB';
To see errors and execution history, you can query the job run details:
SELECT job_name, status, actual_start_date, run_duration, error#
FROM dba_scheduler_job_run_details
WHERE job_name = 'CLEANUP_OLD_ORDERS_JOB'
ORDER BY actual_start_date DESC;
These examples show how Oracle job scheduling works in practice. You define the SQL or PL/SQL logic, attach it to a schedule, and let the database handle execution and logging. This approach keeps automation close to the data, reduces manual intervention, and makes recurring tasks easier to manage and troubleshoot.
Schneide Blog 