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;