ORA-23421: job number xxx is not a job in the job queue

So, This morning I tried to clean up the jobs running in my database.  We have a huge support database where there are around 90 schemas installed. Each schema has its own set of jobs running.

Obviously, this was consuming lot of resources, and we decided to remove the jobs that are not necessary.

I logged in as SYS to scan dba_jobs, and I found there are 500 jobs scheduled

select job, log_user, priv_user,schema_user,what, interval, next_date, broken
from dba_jobs ;

When I tried to remove a job, with dbms_job.job =42, using

dbms_job.remove (42), I got the error below



So, there are 2 options

Option 1
1) Find the Owner of the job from dbms_job table, using schema_user column
2) Log in as schema user and then remove the job. Obviosly, this is time consuming to remove 500 jobs

Option 2
1) Oracle provides another package called dbms_ijob. Both Spec an Body are wrapped, so you cannot find the procedures, but it works pretty similar to dbms_job



Remember to COMMIT, in the end. Otherwise, it will still show up in dba_jobs  in somebody else's session.