Saturday, February 20, 2010

DBMS_SCHEDULER gotcha - ORA-00942: table or view does not exist when trying to drop job

Are you trying to drop a list of jobs like:

BEGIN

DBMS_SCHEDULER.DROP_JOB( job_name=> 'job1,job2...,jobn');

END;

and getting an error?

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 5261

ORA-06512: at "SYS.DBMS_SCHEDULER", line 609

ORA-06512: at line 2

You think this must be a security or permissions issue and you grant "CREATE JOB" and "MANAGE SCHEDULER" to the account, but it still doesn't work. You try running it as SYS and you still get the error.

You search Metalink and get no hits. You search google and you get no hits.

You try just dropping one of the jobs in the list and you get a different error:

ERROR at line 1:

ORA-27475: "XXXX.JOB1" must be a job

ORA-06512: at "SYS.DBMS_ISCHED", line 182

ORA-06512: at "SYS.DBMS_SCHEDULER", line 615

ORA-06512: at line 2

And THAT gives you the hint as to the actual cause of the problem:

ONE OF THE JOBS IN THE LIST HAS ALREADY BEEN DROPPED, OR THE NAME IS WRONG.

Check the jobs against what is currently listed and remove the offender.

Shoot the person that supplied you the list of jobs to be dropped.