HOW TO Kill The Running Job & session in Oracle

1. Check the running Job (From here you will get the SID that running the Job)

SQL> SELECT * FROM DBA_JOBS_RUNNING;

2. Make Job become Broken/offline

BEGIN
SYS.DBMS_JOB.BROKEN(job#,TRUE);
END;

SQL>
BEGIN
SYS.DBMS_IJOB.BROKEN('136451',TRUE);
END;

3. Kill the Oracle’s Session

SELECT SID,SERIAL# FROM v$session where sid in (SELECT sid from dba_jobs_running)

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process ID (PID)

SELECT p.spid FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = :sid;
 
5. Check if the Job is Still Running

SQL> SELECT * FROM DBA_JOBS_RUNNING;

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
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;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue

SQL> SELECT * FROM DBA_JOBS_RUNNING;

9. Mark the DBMS_JOB as Not Broken

BEGIN SYS.DBMS_IJOB.BROKEN(job#,FALSE); END;

10. Alter the Job Queue to Original Value

SQL> ALTER SYSTEM SET job_queue_processes = original_value;


Source.
http://levicorp.com/2009/05/22/how-to-kill-the-running-job/

Thanks.