Stop running job from DBMS JOB package

Stop running job for DBMS_JOB

  1. Find the session id of running job
select sid, job, instance from dba_jobs_running;

2. Find the sid and serial# for kill the running dbms job

SELECT p.spid,
 p.pid,
 s.sid, s.serial#, s.status, s.username,s.program
FROM v$process p, v$session s
WHERE s.paddr(+) = p.addr
AND s.sid=&session_id;

3. Mark the job to broken state, so that it will never restart on session kill

EXEC DBMS_JOB.BROKEN(job#,TRUE);

4. Kill the Session which is running

-- Kill from SQLPLUS
ALTER SYSTEM KILL SESSION 'sid,serial#' immediate;

OR 
--you can kill from OS process id got from second query
--In Windows: 
C:\> orakill <sid> <spid>
--In Linux:
 kill -9 spid

Remove the Broker State after your work done.

EXEC DBMS_JOB.BROKEN(job#,FALSE);
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply