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?
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);