Translate this Page

 

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;

  2 Responses to “HOW TO Kill The Running Job in Oracle”

  1. Excellent hints. It’s very helpful to solve my problem.

  2. Thanks, nice to share the article that can help anyone who have the same problem as me

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha