Scheduling Jobs with Oracle Scheduler

You operate Oracle Scheduler by creating and managing a set of Scheduler objects. Each Scheduler object is a complete database schema object of the form[schema.]name

Job
A job is the combination of a schedule and a program, along with any additional arguments required by the program.


Creating Jobs - Example
Here is the straight forward example to test the scheduler.

1- Create a table for this example, I used SYS schema
CREATE TABLE job_test(ID NUMBER,run_time TIMESTAMP);
2- Create a procedure which will be run by our job

CREATE OR REPLACE PROCEDURE pro_job_test
IS
BEGIN
   INSERT INTO job_test
        VALUES ((SELECT MAX (NVL (ID, 1)) + 1
                   FROM job_test), SYSTIMESTAMP);
   COMMIT;
END pro_job_test;

3- create job class, Job classes allow grouping of jobs with similar characteristics and resource requierments which eases administration. If the JOB_CLASS parameter of the CREATE_JOB procedure is undefined the job is assigned to theDEFAULT_JOB_CLASS.

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              =>  'dwh_jobs',
   comments                    =>  'This is my first job class');
END;

Following query can be used to verify the job class creation

SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES WHERE JOB_CLASS_NAME = 'DWH_JOBS';

4- create the job now
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'MY_JOB_TEST',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'SYS.PRO_JOB_TEST',
   start_date         =>  '08-APR-2013 11.30.00 AM',
   repeat_interval    =>  'SYSDATE+1/1440',
   end_date           =>  '10-APR-2013 11.30.00 AM',
   auto_drop          =>   FALSE,  -- causes the job to persist
   job_class          =>  'dwh_jobs',
   comments           =>  'My new job');
END;

5- Enable job, the job is disabled when it is created

exec DBMS_SCHEDULER.ENABLE ( 'MY_JOB_TEST')

6- Run the below queries to test whether the job worked or not
Qury the job_test table
SELECT * FROM job_test
-- info for the jobs
SELECT job_name,state,job_action ,start_date,repeat_interval,enabled,run_count,failure_count,last_start_date
FROM dba_scheduler_jobs WHERE job_name = 'MY_JOB_TEST'
-- Job run details
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB_TEST';

-- see all running jobs
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

for the column CPU_USED to show valid data, the initialization parameter RESOURCE_LIMIT must be set to true.

-- see job log/status
SELECT LOG_DATE,JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME = 'MY_JOB_TEST' order by LOG_DATE desc ;

Job Logging

You can control the amount of logging that the Scheduler performs on jobs at either a class or job level.

DBMS_SCHEDULER.LOGGING_OFF - No logging will be performed for any jobs in this class.
DBMS_SCHEDULER.LOGGING_RUNS - The Scheduler will write detailed information to the job log for all runs of each job in this class.
DBMS_SCHEDULER.LOGGING_FULL - In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

exec DBMS_SCHEDULER.SET_ATTRIBUTE ( 'MY_JOB_TEST', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
Purging Logs
To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE procedure to specify how much history (in days) to keep.
-- set for the scheduler
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','30');
exec DBMS_SCHEDULER.SET_ATTRIBUTE('dwh_jobs','log_history','30'); -- dwh_jobs here is the job class
-- manually purge logs
exec DBMS_SCHEDULER.PURGE_LOG();
--all entries from the jog log that are older than three days
exec DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');
--Purge all window log entries older than 10 days and all job log entries older than 10 days that relate to MY_JOB_TEST and to the jobs in dwh_jobs
exec DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'MY_JOB_TEST, sys.dwh_jobs'); -- drop a job
exec DBMS_SCHEDULER.DROP_JOB ( job_name => 'MY_JOB_TEST');
-- drop running job
exec DBMS_SCHEDULER.DROP_JOB (job_name=> 'MY_JOB_TEST',force=> TRUE);


Job State with E-mail Notifications
You can configure a job to send e-mail notifications when it changes state.
E-mail notifications can be sent to multiple recipients, and can be triggered by any event in a list of job state events that you specify.

1- Before you can configure jobs to send e-mail notifications, you must set the Scheduler attribute email_server to the address of the SMTP server to use to send the e-mail.

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server', 'mail.server.com');

2- Add the notification. You can include variables like job owner, job name, event type, error code, and error message in both the subject and body of the message. The Scheduler automatically sets values for these variables before sending the e-mail notification.

BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'MY_JOB_TEST',
recipients => 'inam@server.com',
sender => 'do_not_reply@dwhjob.com',
subject => 'Scheduler Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% occurred at %event_timestamp%. %error_message%',
events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/

3-  verify notifications by querying the view USER_SCHEDULER_NOTIFICATIONS.
SELECT JOB_NAME, RECIPIENT, EVENT FROM USER_SCHEDULER_NOTIFICATIONS;
4- [Optional] call ADD_JOB_EMAIL_NOTIFICATION once for each different set of notifications that you want to configure for a job.
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'MY_JOB_TEST',
recipients => 'abuzar@server.com',
events => 'JOB_OVER_MAX_DUR');
END;
/ 

5- [Optional] Use a filter condition to specify that an e-mail notification is to be sent only when the error number that causes the job to fail is 600 or 700
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'MY_JOB_TEST',
recipients => 'inam@server.com',
sender => 'do_not_reply@dwhjob.com',
subject => 'Job Notification-%job_owner%.%job_name%-%event_type%',
body => '%event_type% at %event_timestamp%. %error_message%',
events => 'JOB_FAILED',
filter_condition => ':event.error_code=600 or :event.error_code=700');
END;
/

6- Use the DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION package procedure to remove e-mail notifications for a job.
BEGIN
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
job_name => 'MY_JOB_TEST',
recipients => 'abuzar@server.com',
events => 'JOB_DISABLED, JOB_SCH_LIM_REACHED');
END;
/

--verify the removal
SELECT JOB_NAME, RECIPIENT, EVENT FROM USER_SCHEDULER_NOTIFICATIONS;
Test Mail Notification
1- Modify proc (created earlier here)

CREATE OR REPLACE PROCEDURE pro_job_test
IS
BEGIN
   INSERT INTO job_test
        VALUES ((SELECT MAX (NVL (ID, 1)) + 1
                   FROM job_test), SYSTIMESTAMP);

   COMMIT;
   dbms_lock.sleep(100);
END pro_job_test;

2- Check job status
SELECT
job_name,state,job_action ,start_date,repeat_interval,enabled,run_count,failure_count,last_start_date
FROM dba_scheduler_jobs WHERE job_name = 'MY_JOB_TEST'

3- Add a new notification for the stopped event also
BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name => 'MY_JOB_TEST',
recipients => 'inam@server.com',
events => 'JOB_STOPPED');
END;
4- Test it now
-- run the job (using SQL Plus) and cancel by CTRL+C
exec DBMS_SCHEDULER.run_job (job_name => 'MY_JOB_TEST');
You will get mail like below

--Stop the job
exec DBMS_SCHEDULER.stop_job (job_name => 'MY_JOB_TEST');
You will get mail like below

5- Check additional info
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB_TEST'; 

Removing Job Notification
BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'MY_JOB_TEST',
  recipients =>  ',abuzar@server.com,usman@server.com',
  events     =>  'JOB_FAILED');
END;