Killing DBMS_JOB

To check scheduled job:

scheduled_dbms_jobs.sql

set line 200 pages 200
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'LastDate'
col this_date for a18 head 'ThisDate'
col next_date for a18 head 'NextDate'
col interval for 9999999.000 head 'RunInterval'
col what for a60

select j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

What Jobs are Actually Running

select * from dba_jobs_running;

running_jobs.sql

set linesize 250
col sid for 9999 head 'SessionID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'LastDate'
col this_date for a18 head 'ThisDate'
col next_date for a18 head 'NextDate'
col interval for 9999.000 head 'RunInterval'
col what for a60

select j.sid,j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.what
from (select djr.SID, dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs

session_jobs.sql

select j.sid,s.spid,s.serial#,j.log_user,j.job,j.broken,j.failures,j.last_date':'j.last_sec last_date,j.this_date':'j.this_sec this_date,j.next_date':'j.next_sec next_date,j.next_date - j.last_date interval,j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES, dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC, dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down

2. Mark the DBMS_JOB as Broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

3. Kill the Oracle Session

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill -9 spid

5. Check if the Job is Still Running

6. Determine the Current Number of Job Queue Processes

SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

8. Validate that No Processes are Using the Job Queue (Re-run the session_jobs.sql ).

9. Mark the DBMS_JOB as Not Broken

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE);

10. Alter the Job Queue to Original Value

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.