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

ORA-23421: job number xxx is not a job in the job queue
 
I tried to clean up the jobs running in my database.  
We have a huge support database where there are around 30 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, last_date, what from dba_jobs where log_user like ‘ERA%’;
 
Inline image 2
When I tried to remove a job, with dbms_job.job =42, using
 
dbms_job.remove (11), I got the error below 
 
Inline image 1
 
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
Inline image 3
 
Remember to COMMIT, in the end. Otherwise, it will still show up in dba_jobs  in somebody else’s session.

About sunkupuli

Database Administrator
This entry was posted in Database, Oracle. Bookmark the permalink.

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