Script to find BROKEN JOBS

Broken Jobs
--------------

SQL> select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,FAILURES,WHAT from dba_jobs where BROKEN='Y';

       JOB LOG_USER   PRIV_USER  SCHEMA_USE   FAILURES WHAT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
        55 SYS        SYS        SYS                16    DAILY_PRODUCT_MOVEMENT_LOAD(50000);



col what for a45
set linesize 200
select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,FAILURES,WHAT from dba_jobs where BROKEN='Y';

       JOB LOG_USER        PRIV_USER       SCHEMA_USER       FAILURES WHAT
---------- --------------- --------------- --------------- ---------- --------------------
        26 SECADMIN        SECADMIN        SECADMIN                16 CLEAN_AUDIT;



Mark a job as not broken

If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset, issue command:

EXEC dbms_ijob.broken(jobno, FALSE);----  to kill other users jobs as well
EXEC dbms_job.broken(jobno, FALSE);----  to kill only the users in which you gets connected