Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, April 07, 2013

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;

1 comment:

john bill said...

Wonderful appreciated it is very informative and helpful for everyone keep updates, see oracle community network for oracle business consulting.