Stop running job for DBMS_JOB
- 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);