Query session_jobs.sql (running jobs and their SIDs and SPIDs): 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;
Source: http://blogs.ittoolbox.com/database/solutions/archives/killing-the-oracle-dbms_job-6498