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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.