Detecting Broken DBMS_JOBS

Jobs are automatically re-tried when it fails. However, after 16 re-tries, the job is marked as BROKEN and will not be tried again. Here is a query that will monitor the Job Status so that it can send a SMS to the DBA when broken jobs are detected.

DECLARE
     BROKEN_JOBS_DETECTED EXCEPTION;
     V_COUNT NUMBER;
BEGIN
     SELECT COUNT(*) INTO V_COUNT FROM DBA_JOBS WHERE BROKEN='Y';
     IF (V_COUNT > 0) THEN
          RAISE BROKEN_JOBS_DETECTED;
     END IF;
EXCEPTION
    WHEN BROKEN_JOBS_DETECTED THEN
           RAISE_APPLICATION_ERROR (-20199,'Broken Jobs Detected');
END;
/
EXIT

So in the command file:

sqlplus user/password@instance @broken_job.sql
if %ERRORLEVEL% = 20199 sendmail email@address "Broken Job Detected" "Additional Text"

The sendmail is a 4NT command. You can replace it by any command line e-mail. In 4NT.INI, you need an entry for your 'MailServer=pop.address.of.email' and 'MailAddress=email@address'.