You can remove a job from the database by issuing a DROP_JOB statement, as in the following:
job_name => 'my_job1');
refer this link:
Unfortunately when I execute exec dbms_job.remove(94)
I get an error saying that job 94 is not in the job queue.
Just to recap what has happened, I droped the jobs using TOAD but it has not removed them from the DBA_JOBS table.
I assume that this happened to you:
mob@MOB92.WORLD> exec dbms_job.remove(24)
BEGIN dbms_job.remove(24); END;
ERROR at line 1:
ORA-23421: job number 24 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1
This error is caused when you try to execute dbms_job.remove(id) for a job that does not belong to you. You have to be a job owner to execute dbms_job.remove
Krystian Zieja / mob
SQL>select job,schema_user,priv_user from user_jobs; ORA-23421: job number string is not a job in the job queue Cause: There is no job visible to the caller with the given job number. Action: Choose the number of a job visible to the caller.
As you have probably realized dbms_scheduler refers to Oracle Scheduler jobs which are different to dbms_job jobs (dbms_scheduler is the 10g replacement for dbms_job and should be used in 10g or higher) .
Apart from summarizing what was said above (only the owner of a dbms_job job can dbms_job.remove it and you need to commit after remving) the only other thing I can think of that might help is that querying the underlying table may show more clearly what is happening .
Selecting from sys.job$ will show you the data in the underlying table that dbms_job is seeing (powner is the real owner of the jobs). Once you have figured out the job number and owner from there all you should have to do is login as that user , do dbms_job.remove and commit . Also selecting from user_jobs as a particular user should show only jobs owned by that user.
Hope this helps,
Have you tried using dbms_ijob?
logon as sysdba and use:
to drop all users jobs
or to drop a specific job