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:
begin dbms_job.broken(15609,true); end;
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", b.name "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.