Kill The Running Job in Oracle

Some times it becomes necessary to kill the ongoing running Oracle job. I faced a situation when there was "enq: TX - row lock contention" and job was continuously running.


1- Check the running Job (From here you will get the SID that running the Job)
SELECT * FROM DBA_JOBS_RUNNING;

SID	JOB	FAILURES	LAST_DATE	LAST_SEC	THIS_DATE	THIS_SEC	INSTANCE
20	366	0	4/7/2013 10:03:14 AM	10:03:14			0

2- Make Job become Broken/offline
BEGIN SYS.DBMS_IJOB.BROKEN(366,TRUE); END;
3- Kill the Oracle’s Session
SELECT SID,SERIAL#,LAST_CALL_ET,TERMINAL,PROGRAM,STATUS,EVENT
FROM V$SESSION
WHERE SID=20

SID	SERIAL#	LAST_CALL_ET	TERMINAL	PROGRAM	STATUS	EVENT
20	929	828	S-HO-SN-OR-01	ORACLE.EXE (J001)	ACTIVE	enq: TX - row lock contention

 
ALTER SYSTEM KILL SESSION '20,929';

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 = 20

SPID
2904

For Windows, at the DOS Prompt: orakill sid spid 
For UNIX at the command line> kill '9 spid

5-  Check if the Job is Still Running
SELECT * FROM DBA_JOBS_RUNNING;
If still running then 
Determine the Current Number of Job Queue Processes
select name,value from v$parameter where name = 'job_queue_processes';

NAME	VALUE
job_queue_processes	1000

6- Alter the Job Queue to Zero
ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.

7- Validate that No Processes are Using the Job Queue
SELECT * FROM DBA_JOBS_RUNNING;

8- Mark the DBMS_JOB as Not Broken
BEGIN SYS.DBMS_IJOB.BROKEN(363,FALSE); END;
9- Alter the Job Queue to Original Value
 ALTER SYSTEM SET job_queue_processes = 1000;