Wednesday 24 November 2010

ORA-23421: job number <nnn> is not a job in the job queue

You can only change your own jobs by using DBMS_JOB package. Not even SYS is exception to this, meaning that if you connect to the database as SYSDBA you'll be only able to change jobs belonging to SYS, but not those belonging to SCOTT, for example.

Howevere there is undocumented package called DBMS_IJOB that enables you to manipulate other users jobs (provided that you have EXECUTE ANY PROCEDURE privilege). For example, you can remove any job (not only those owned by you) from the job queue by executing procedure SYS.DBMS_IJOB.REMOVE(:job_id)

I'm not sure if DBMS_IJOB has all the procedures that DBMS_JOB has (WHAT, NEXT_DATE, INTERVAL, BROKEN, ...) and if all the parameters are the same, but you can find this out by yourself.

logon as sysdba and use:

SQL>select * from dba_jobs;

To drop perticular job:

SQL>exec SYS.DBMS_IJOB.remove(210);

To drop all user’s job:

SQL>exec SYS.DBMS_IJOB.DROP_USER_JOBS('myuser');

Cheers!

Job Removed!

No comments: