Killing Oracle Jobs

This article is not purely mine, I take this from another source, which I already forgot. However, I already used this to solve several issue regarding Oracle Jobs. To solve this issue will require Oracle SYS and Oracle OS user. Let start with listing the jobs

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

Now, lets find the job the actually running. From this query identify the jobs that will be killed, it can be identified by WHAT column.

select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Before starting to kill the job, Job Queue Process must be stopped, first check what is the current value of this parameter

show parameter job_queue_processes;

Keep the value (usually 10) then make the value to 0

ALTER SYSTEM SET job_queue_processes = 0;

Now lets start killing the job by marking the job as broken

exec DBMS_JOB.BROKEN(job#,TRUE);
commit;

Find the job# from the second query column JOB of the job that will be killed,  after that kill the session of that job

ALTER SYSTEM KILL SESSION 'sid,serial#';

Find the sid and serial# from second query column SID and SERIAL#. However, when the job is still running, then killing OS process must be done. Login to the DB server as Oracle user, then from the second query SPID column, the OS pid can be identified.

ps -ef | grep SPID
kill -9 SPID

or in Windows

orakill DB-SID SPID

Check using the second query if the jobs is still running, when there is none then the job can be flagged as not broken

exec DBMS_JOB.BROKEN(job#,FALSE);

Make the value of job_queue_process to its original value

ALTER SYSTEM SET job_queue_processes = original_value;

Just to make sure run the first and second query to see if the job is not broken or whatsoever.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s