Reset Sched. Job Failure Counts

You could rest failure count on a database after a database crash using the following script. This basically disables and enables all scheduler jobs with non zero failure count.

DECLARE
   CURSOR c_job_list
   IS
      SELECT owner, job_name
        FROM dba_scheduler_jobs
       WHERE failure_count > 0 AND max_failures IS NOT NULL; 

   l_owner      dba_scheduler_jobs.owner%TYPE;
   l_job_name   dba_scheduler_jobs.job_name%TYPE;
BEGIN
   OPEN c_job_list; 

   LOOP
      FETCH c_job_list
       INTO l_owner, l_job_name; 

      EXIT WHEN c_job_list%NOTFOUND;
      DBMS_SCHEDULER.DISABLE (NAME       => l_owner || ‘.’ || l_job_name,
                              FORCE      => TRUE
                             );
      DBMS_SCHEDULER.ENABLE (NAME => l_owner || ‘.’ || l_job_name);
   END LOOP;
END;
/

SELECT owner, job_name, max_failures, failure_count
  FROM dba_scheduler_jobs
 WHERE failure_count > 0 AND max_failures IS NOT NULL;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: