Re: How to best schedule jobs in another schema?

From: Kurt Franke <>
Date: Sat, 13 Feb 2010 20:48:44 +0100 (CET)
Message-ID: <16531299.864724.1266090524188.JavaMail.fmail_at_mwmweb033>


>So how should one schedule jobs to run in another schema? The schemas I'm
>trying to run jobs in cannot be logged into for security purposes (have an
>invalid password). The only thing I can come up with is to write a
>DBMS_SCHEDULER wrapper package to be created in each schema, but this is a
>maintenance nightmare. Either that, or use a generic "execute immediate"
>proc in each schema, but the security implications of that should be
>Anyone using DBMS_SCHEDULER to run jobs in another schema? How do you use it?

In the old Mechanism with dbms_job there was also a Package dbms_ijob which allowed to create the job for another user or just change one or all of the users (LOG_USER, PRIV_USER, SCHEMA_USER in dba_jobs) In a tool I always created the job with the normal package submit call as disabled and then changed the owner with dbms_ijob.CHANGE_ENV() call, just to have an submit cal las simple as possible without all the additional parameters of dbms_ijob.submit() I always used the same user for all three users as done by the normal submit call. I used this for years to automatically create jobs in a tool for 6 years without problems.  

Because there exists a package dbms_isched with analogous calls as dbms_scheduler but with additional parameters in the procedures like INVOKER and SYS_PRIVS I would assume creating jobs of another user ccould be done with it. There is no counterpart to the dbms_ijob.change_env() procedure, but others like copy_job() with the ability to copy it to another user or run_job() with the ability to run it under another user.  



Received on Sat Feb 13 2010 - 13:48:44 CST

Original text of this message