Identify and Kill Running Jobs



My OraFAQ Blog

Contact me

Run this query to identify which jobs are currently running:

select dbr.sid, s.serial#, s.username, dbj.* 
from dba_jobs_running dbr, dba_jobs dbj , v$session s
where dbr.job=dbj.job and s.sid=dbr.sid;

Note: On 9i databases, you may wish to add a /*+ rule */ hint as dba_jobs_running performs very poorly. This is not an issue for 8i or 10g.

If you want to kill a running job, do it in two steps. First, mark the job as Broken:


Marking the job as Broken is necessary; otherwise, the job queue process will restart the job as soon as it notices the job has been killed.

The next step is to kill the database session running the job. Use the SID and serial# selected in the query above to identify the session that needs to be killed. For example, for a SID of 115 and serial# of 21914:

alter system kill session '115,21914' immediate;

In certain cases, Oracle may be unable to kill the session running the job, in which case you'll get the error "Session marked for kill," and the session's status may change to KILLED. A case in point is when the job is running a distributed transaction, such as a SELECT from a remote database. In this situation, you may need to kill the session at the OS level (not possible if using MTS).

To identify which OS process (*nix) or thread (Windows) is associated with the running session, run a query like this one:

select p.spid "OS PID", "Background Process", 
s.sid, s.username "User Name", s.osuser "OS User", 
s.machine "User Machine"
from v$process p, v$bgprocess b, v$session s
where s.sid=115 --put your SID here
and s.paddr=p.addr
and b.paddr(+) = p.addr ;

On Unix, you can then use the kill command to kill the process; on Windows, use the orakill command to kill the thread.

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...