Oracle jobs - status, enabling or disabling them

Check the status of Oracle jobs:

You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken, so that Oracle does not attempt to run the job again.

After you have identified the session running the job (using V$SESSION or V$LOCK, as shown earlier), you can disconnect the session using the SQL statement ALTER SYSTEM.

Display Oracle jobs:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;

Display all running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB;

Script to generate SQL to disable or enable all jobs:

Disable all jobs:
select 'exec dbms_ijob.broken('||job||',true);' from dba_jobs;

Enable all jobs:
select 'exec dbms_ijob.broken('||job||',fales);' from dba_jobs;