HOW TO Kill The Running Job in Oracle

Sometime we have frustrated to kill the running Job in Oracle even though we already kill him from OS but the job still running. Using this step could kill your stress, I mean your running Job. Good Luck –

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_IJOB.BROKEN(job#,TRUE); END;

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

3. Kill the Oracle’s Session

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;


  

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

Ussually Oracle Job running with Process Name : ora_j00%

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;