Killing an Oracle job at the OS level

 

1. Find the job we what to kill and the session associated with the job

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;





2. Mark the job as Broken



begin

DBMS_JOB.BROKEN(job#,TRUE);

commit;

end;



NOTE: After executing this command the job is still running.





3. Kill the Oracle Session



ALTER SYSTEM KILL SESSION 'sid, serial#';



NOTE: In many situations the session is marked 'KILLED' but is not killed.





4. Kill the O/S Process



For UNIX:
kill -9 spid

For Windows at the DOS Prompt:
orakill sid spid





Note: The article ( named Killing an Oracle job at the OS level ) was taken from www.in-oracle.com.