Manage other user's jobs with DBMS_IJOB...

I had a user that called me about jobs not running on a 9i database. I logged in and found a complete mess. Broken jobs, some DBMS_JOB calls would hang, jobs existed for users that no longer existed (I didn't even know that was possible!).

Because things were such a mess I just wanted to remove all the jobs and start from scratch. But I needed to drop jobs for a user that no longer existed. Using DBMS_JOB even SYS does not have complete control of jobs. How can I remove a job for a user that no longer exists?

Enter DBMS_IJOB.

I couldn't find anything about this in Oracle's "Supplied PL/SQL Packages" documentation but came across this helpful package yesterday after some Google and MetaLink searches.

DBMS_IJOB is a package included with Oracle that allows a sys or system user to manipulate other user's jobs. Now I wouldn't use this package as the primary interface for updating jobs but if you are in a pinch it is worth a shot before opening a TAR.

I don't know if it has all the same functions as DBMS_JOB but here are the three functions that got me out of my bind:

exec dbms_ijob.remove(jobnum);
exec dbms_ijob.broken(jobnum,true);
exec dbms_ijob.run(jobnum);

-- Dave