Oracle DBMS_SCHEDULER: Creating and Managing Jobs
The DBMS_SCHEDULER package in Oracle is a powerful tool for scheduling and automating tasks. Unlike its predecessor, DBMS_JOB, it provides advanced features such as calendaring expressions, better logging, and robust error handling.
This article explores the key operations you can perform with DBMS_SCHEDULER, including creating, running, modifying, and monitoring jobs.
Creating a Job
To create a job, you can use the CREATE_JOB procedure. Here’s an example of creating a job that runs an executable script:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TEST_JOB',
job_type => 'EXECUTABLE',
job_action => '/u01/app/abc.',
repeat_interval => 'FREQ=MINUTELY',
enabled => TRUE
);
END;
/
Explanation of Parameters:
job_name: The name of the job. In this case, it’s TEST_JOB.
job_type: Specifies the type of job. Here, it’s EXECUTABLE.
job_action: The action the job performs. In this case, it points to the script file /u01/app/abc..
repeat_interval: Defines how often the job runs. FREQ=MINUTELY means it runs every minute.
enabled: A boolean flag indicating whether the job should be enabled immediately.
Note: Unlike DBMS_JOB, you don’t need to commit the job creation for it to be active. To cancel the job, you must explicitly disable or remove it.
Removing a Job
To delete a job, use the DROP_JOB procedure:
EXEC DBMS_SCHEDULER.DROP_JOB('TEST_JOB');
Running a Job Immediately
If you want to execute a job manually, use the RUN_JOB procedure:
EXEC DBMS_SCHEDULER.RUN_JOB(‘TEST_JOB’);
This forces the job to run immediately, regardless of its scheduled time.
Changing Job Attributes
You can modify job attributes, such as the duration or repeat interval, using the SET_ATTRIBUTE procedure.
Example 1: Change Job Duration
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
Example 2: Update Repeat Interval
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
'WEEKNIGHT_WINDOW',
'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0'
);
END;
/
Enabling or Disabling a Job
To activate or deactivate a job, use the ENABLE and DISABLE procedures.
Enable a Job:
BEGIN
DBMS_SCHEDULER.ENABLE('TEST_JOB');
END;
/
Disable a Job:
BEGIN
DBMS_SCHEDULER.DISABLE('TEST_JOB');
END;
/Monitoring Jobs
You can monitor the status of a job using the DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_LOG views.
Example Queries:
Check Job Details:
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'TEST_JOB';
View Job Execution Logs:
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'TEST_JOB';
Monitoring Jobs as the Owner
If you are the schema owner of the job, use the following views instead:
Check Job Details:
SELECT * FROM user_scheduler_jobs WHERE job_name = 'TEST_JOB';
View Job Execution Logs:
SELECT * FROM user_scheduler_job_log WHERE job_name = 'TEST_JOB';
Conclusion
The DBMS_SCHEDULER package provides a robust framework for scheduling and managing jobs in Oracle. With its flexible scheduling, extensive logging, and advanced monitoring features, it is a powerful tool for automating database tasks. Use the examples provided to streamline and optimize your database operations.
Home 