Alerts - Scheduler Jobs Did Not Run

The last couple of days our warehouse environment hasn't been updating properly. It's basically a collection of Materialized Views based on the OLTP system. It appears as though many (all?) of our scheduler jobs are not running. DBA believes it has something to do with the Flash Recovery Area running out of space. I don't know and I don't have the time to find a root cause. I do know that there were no errors and the jobs were not in a failed status.

Rather than have the business inform us their reports our not right I was tasked with creating an alert if the jobs did not run. Usually we set up alerts for things that break or actually completed. I think this is the first time I've had to build something to the opposite.

Here's what I came up with:

CREATE OR REPLACE
PROCEDURE jobs_did_not_run
AS
  b VARCHAR2(3) := CHR(10);
  TYPE r_table IS RECORD
  (
    job_name VARCHAR2(30),
    next_run_date DATE,
    job_action VARCHAR2(4000)
  );
  TYPE t_table IS TABLE OF R_TABLE;
  l_table T_TABLE := T_TABLE();
  l_subject VARCHAR2(50) := 'Alert - Jobs have not run';
  l_message VARCHAR2(32767);
BEGIN
  SELECT job_name, next_run_date, job_action
  BULK COLLECT INTO l_table
  FROM dba_scheduler_jobs
  WHERE state = 'SCHEDULED'
    AND enabled = 'TRUE'
    AND next_run_date < SYSDATE;
 
  l_message := 'The following jobs have not run today:' || b;
  FOR i IN 1..l_table.COUNT LOOP
    l_message := l_message || 'Job:           ' || 
                   l_table(i).job_name || b;
    l_message := l_message || 'Next Run Date: ' || 
                   TO_CHAR( l_table(i).next_run_date, 'MM/DD/YYYY HH24:MI:SS' ) || b;
    l_message := l_message || 'Action:        ' || 
                   l_table(i).job_action || b;
  END LOOP;

  utl_mail.send
    ( sender => '',
      recipients => '',
      subject => l_subject,
      message => l_message );

END jobs_did_not_run;
/
show errors

You can find it here in my Google Code home for DBA Utilities. I do believe that at some future point this will be incorporated into a package, but for now it is a standalone procedure.

You'll receive a nice little email noting the JOB_NAME, scheduled NEXT_RUN_DATE and the JOB_ACTION (the anonymous block).