tags:

views:

37

answers:

1

Ok, database at a clients site that has dbms_job entries where the schema_user is invalid. (It appears to be the effect of bringing over a schema from another machine using exp/imp.)

I would like to get rid of these jobs, but standard operating procedure says that you must connect as the owner of the jobs to dbms_job.remove() it.

I thought a workaround might be, to create the user in this instance, and then use it to remove the job.

Thoughts?

Edit: Or even alternatively making direct edits to the sys.job$ table instead of going through the dbms_job interface?

+4  A: 

There's a package owned by SYS called DBMS_IJOB. This offers pretty much the same functionality as DBMS_JOB but it allows us to manipulate jobs owned by other users.

If your rogue job is number 23 then this command should kill it:

SQL>  exec dbms_ijob.remove(23)

By default privileges on this package are not granted to other users, so you need to connect as SYS in order to execute it. And remember to commit the change!

APC
You sir are a scholar and a gentleman. Thanks very much!
jskaggz