DBA_JOBS marked broken after 16 tries

Recently I had issues with materialized view replication and the job was marked broken after 16 tries. After the replication problem was fixed running the job manually fixed the execution issue and job was back on schedule.

Oracle DBMS_JOBS will try to execute the just for 16 times before mark it BROKEN.

To check if a job is broken one can run the following query:
SQL> SELECT job, to_char(last_date, ‘DD-MON-YYYY HH24:MI:SS’), broken FROM dba_jobs
WHERE broken = ‘Y’;

Once the DBA_JOBS flag is marked broken the only way to change that flag is either recreating the job or run it manually.

exec dbms_job.run(<job_num>);

Hope that helps.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.