How to Deal with Jobs Belonging to Other Users

Posted in: Technical Track

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…




Want to talk with an expert? Schedule a call with our team to get the conversation started.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *