Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations.

## Job Owner Schema can do this

execute dbms_job.remove(444);
execute dbms_job.broken(444, true);

## If Sys User needs to remove the job

exec SYS.DBMS_IJOB.REMOVE(444);

## to find which jobs are running
SELECT a.sid,
c.serial#,
a.job,
a.failures,
to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime,
b.what
FROM dba_jobs_running a, dba_jobs b, v$session c
WHERE a.job = b.job
AND a.sid = c.sid
order by a.this_date

##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database

select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS
from gv$process p, gv$session s
where P.ADDR=S.PADDR
and S.SID=888 and s.serial#=9293;

## kill the job
alter system kill session '173,18465';

## make the job broken
execute dbms_job.broken(444, true);

## remove the job
execute dbms_job.remove(444);

## If you are not owner of the job you can drop any job using sys user by
exec SYS.DBMS_IJOB.REMOVE(444);

If job removing process hangs please try with following sereis of command

ALTER SYSTEM SET job_queue_processes = 0 SCOPE=MEMORY;
execute dbms_ijob.remove(444);
ALTER SYSTEM SET job_queue_processes = 10 SCOPE=MEMORY;