How to Deal with Jobs Belonging to Other Users

Jul 16, 2012 / By Fabio Zea


So this is my first official entry on my brand new blogging account. I kept postponing doing this, as I was always too busy with something more important to do. Anyway, today I ran into this issue and figured that it was as good a time as any to start blogging. So without further ado, here it is!

This is not the first time I have run into this issue – in the past I (and who knows how many more Oracle DBAs) have had to deal with handling jobs from a different user, which usually ends up with a big:

ERROR at line 1:
ORA-23421: job number XX is not a job in the job queue
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.DBMS_IJOB”, line 770
ORA-06512: at “SYS.DBMS_JOB”, line 254
ORA-06512: at line 1

And of course, we don’t know the password for the real owner of the job. So first, we identify the real owner of the job (select job, schema_user from dba_jobs). Second, we figure out a way to connect as the real owner and let dbms_job do the magic.

However, when you are dealing with several clients/db_users/databases, there isn’t always time to find a way to connect with the owner and use dbms_job. The answer to my prayers was the undocumented dbms_ijob package, which lets me, as a DBA, deal with jobs from other users.

I don’t need to elaborate on the usage of dbms_job. If you’re here it means that you have already used it and played with it.

From what I have experienced so far, the dbms_ijob package can be used the same way as dbms_job, so have fun with it.

Until my next post…


Share this article

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>