DBMS_SCHEDULER.CREATE_JOB in Oracle

🛠 A Comprehensive Guide to DBMS_SCHEDULER.CREATE_JOB in Oracle

Automating tasks in Oracle Database is a common requirement in enterprise environments. Whether it's executing a PL/SQL block, invoking a stored procedure, or running an external OS script, the DBMS_SCHEDULER package offers powerful scheduling capabilities.

One of the key procedures is CREATE_JOB. In this post, we’ll explore all its parameters with detailed explanations and examples.

---

🎯 What is DBMS_SCHEDULER.CREATE_JOB?

DBMS_SCHEDULER.CREATE_JOB allows you to create and schedule jobs in Oracle. It supports various job types like PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE. The flexibility to run jobs based on calendar expressions or intervals makes it a robust tool for database administrators and developers.

---

📜 Syntax Overview

DBMS_SCHEDULER.CREATE_JOB (
job_name => 'your_job_name',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN your_code; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=1;',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Optional comment'
);

---

📋 List of All Parameters

Below is a detailed table of all supported parameters in DBMS_SCHEDULER.CREATE_JOB:

Parameter Type Description

job_name VARCHAR2 Unique name of the job (can be schema-qualified).
job_type VARCHAR2 Job type: 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', 'CHAIN', 'BACKUP_SCRIPT'.
job_action VARCHAR2 What the job should do: PL/SQL block, procedure name, or external command.
number_of_arguments BINARY_INTEGER Number of arguments (used for jobs with arguments).
start_date TIMESTAMP WITH TIME ZONE When the job should start running.
repeat_interval VARCHAR2 Calendar expression or frequency string (e.g., 'FREQ=DAILY;BYHOUR=1;').
end_date TIMESTAMP WITH TIME ZONE Optional stop time for job execution.
job_class VARCHAR2 Classification for resource management (default: DEFAULT_JOB_CLASS).
enabled BOOLEAN If set to TRUE, job starts as scheduled. If FALSE, manual enabling is required.
auto_drop BOOLEAN If TRUE, job is dropped after completion.
comments VARCHAR2 Description or notes for the job.
credential_name VARCHAR2 Required for external jobs; defines OS credentials.
destination_name VARCHAR2 Used for remote job execution.
logging_level NUMBER Set to LOGGING_OFF, LOGGING_RUNS, or LOGGING_FULL.
stop_on_window_close BOOLEAN Stops the job when its resource window closes.
instance_id NUMBER Specifies instance (in RAC environments).
job_style VARCHAR2 REGULAR or LIGHTWEIGHT. Lightweight jobs are optimized for frequent execution.
parallel_instances BOOLEAN For RAC: allow job to run on multiple instances.
raise_events NUMBER Raise events for job status changes (e.g., success, failure).
system BOOLEAN Reserved for Oracle internal use.

---

✅ Common Use Case Example

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'daily_backup_job',
job_type => 'STORED_PROCEDURE',
job_action => 'backup_pkg.perform_backup',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
job_class => 'DEFAULT_JOB_CLASS',
enabled => TRUE,
auto_drop => FALSE,
comments => 'Daily backup job for critical data',
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
raise_events => DBMS_SCHEDULER.JOB_FAILED + DBMS_SCHEDULER.JOB_SUCCEEDED
);
END;
/

---

📎 Notes

After a job is created with enabled => FALSE, you must manually enable it using:

BEGIN
DBMS_SCHEDULER.ENABLE('job_name');
END;

To monitor job runs and failures:

SELECT job_name, status, run_duration, error#
FROM dba_scheduler_job_run_details
WHERE job_name = 'YOUR_JOB_NAME';

---

🧠 Final Thoughts

Oracle’s DBMS_SCHEDULER is a powerful utility that can be leveraged for everything from routine maintenance to complex ETL workflows. Understanding each parameter of the CREATE_JOB procedure allows you to craft highly customized and efficient job schedules.