How to Schedule Queries in Oracle

How do I schedule SQL queries automatically in Oracle?

DBMS_SCHEDULER lets you run SQL or PL/SQL automatically at defined times or intervals.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why schedule queries in Oracle?

Automating recurring SQL eliminates manual execution, ensures reports are refreshed on time, and frees developers for high-value work. Jobs created with DBMS_SCHEDULER run inside the database, inherit server resources, and can log output for auditing.

What roles and privileges are required?

Grant CREATE JOB and, if needed, MANAGE SCHEDULER. Developers without DBA rights can still create jobs inside their schema when these privileges are enabled.

What is the minimal CREATE_JOB syntax?

Use DBMS_SCHEDULER.CREATE_JOB with job_name, job_type, job_action, and schedule_name (or inline schedule). Optionally set enabled => TRUE to start immediately.

How do I schedule a reporting query hourly?

Create a reporting table, then schedule an INSERT INTO that copies aggregated data every hour:

BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'hourly_sales_totals', job_type => 'PLSQL_BLOCK', job_action => 'INSERT INTO sales_totals SELECT TRUNC(SYSDATE, ''HH''), SUM(total_amount) FROM Orders GROUP BY TRUNC(order_date, ''HH'');', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=HOURLY', enabled => TRUE); END; /

How can I reuse schedules?

Define a named schedule once, then attach it to multiple jobs with schedule_name. Example: DBMS_SCHEDULER.CREATE_SCHEDULE to build hourly_interval.

How to pass parameters?

Wrap your logic in a stored procedure. In CREATE_JOB set job_type => 'STORED_PROCEDURE' and job_action => 'pkg_reports.gen_customer_kpi'. Arguments are defined inside the procedure signature.

How to monitor and manage jobs?

Query USER_SCHEDULER_JOBS and USER_SCHEDULER_JOB_RUN_DETAILS. Use DBMS_SCHEDULER.RUN_JOB, STOP_JOB, and DROP_JOB for control.

What are best practices?

Keep job names descriptive, log errors with JOB_CLASS, restrict privileges, and avoid heavy logic in anonymous blocks; call procedures instead.

Common mistakes and fixes

Missing grants: Without CREATE JOB your script fails. Ask a DBA to grant it.
Forgetting time zone: Use SYSTIMESTAMP and specify repeat_interval in calendar syntax to prevent DST surprises.

FAQ

Can I run external OS scripts?

Yes—set job_type = 'EXECUTABLE' and give the database host user permission to run the file.

How do I disable a job temporarily?

Run DBMS_SCHEDULER.DISABLE('job_name'). Re-enable with ENABLE.

Does DBMS_JOB still work?

It is supported but deprecated. New code should use DBMS_SCHEDULER for richer features and time-zone awareness.

Why How to Schedule Queries in Oracle is important

How to Schedule Queries in Oracle Example Usage


-- Run a weekly inventory check and email low-stock items
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'weekly_inventory_alert',
    job_type        => 'PLSQL_BLOCK',
    job_action      => $$
      DECLARE
        v_low_stock SYS_REFCURSOR;
      BEGIN
        OPEN v_low_stock FOR
          SELECT name, stock FROM Products WHERE stock < 10;
        pkg_notify.send_inventory_alert(v_low_stock);
      END;$$,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=8',
    enabled         => TRUE);
END;
/

How to Schedule Queries in Oracle Syntax


BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'daily_customer_export',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'INSERT INTO customer_backup
                        SELECT * FROM Customers
                        WHERE created_at >= TRUNC(SYSDATE)-1;',
    start_date      => TO_TIMESTAMP('2024-06-04 02:00:00','YYYY-MM-DD HH24:MI:SS'),
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    enabled         => TRUE,
    comments        => 'Nightly customer snapshot');
END;
/

-- Parameters
-- job_name: Unique identifier
-- job_type: PLSQL_BLOCK | STORED_PROCEDURE | EXECUTABLE
-- job_action: Code block, procedure name, or shell path
-- start_date: First run timestamp
-- repeat_interval: Oracle calendaring expression
-- enabled: TRUE to start immediately

Common Mistakes

Using DBMS_JOB instead of DBMS_SCHEDULER: DBMS_JOB lacks time-zone and calendar features. Migrate to DBMS_SCHEDULER for reliable scheduling.
Hard-coding dates in repeat_interval: This stops jobs after the date passes. Use relative calendar syntax like 'FREQ=DAILY' to keep jobs running indefinitely.