This discussion is archived
13 Replies Latest reply: Feb 27, 2013 6:47 AM by spajdy RSS

DBMS_SCHEDULER.run_job

Emu Newbie
Currently Being Moderated
Hi Guys,
I'm having a problem with using DBMS_SCHEDULER.

When I run with the current session set to true the job works, however when its not run as the current session (the same session that created the job) then it fails to execute. (The package that seems to be the problem in on a remote database)

So I figure I need to give permissions on the problem package to um something. But um who do I grant the permisisons to?

begin
DBMS_SCHEDULER.run_job('myjob',use_current_session => true);
end;
works

begin
DBMS_SCHEDULER.run_job('myjob',use_current_session => false);
end;
fails
  • 1. Re: DBMS_SCHEDULER.run_job
    sb92075 Guru
    Currently Being Moderated
    privileges acquired via ROLE do not apply within named PL/SQL procedures.

    who is executing posted code?
    who owns the procedure?
  • 2. Re: DBMS_SCHEDULER.run_job
    rp0428 Guru
    Currently Being Moderated
    >
    So I figure I need to give permissions on the problem package to um something. But um who do I grant the permisisons to?
    >
    The answer to that is in the docs.
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm
    >
    use_current_session
    This specifies whether the job run should occur in the same session as the one that the procedure was invoked from.

    When use_current_session is set to TRUE:

    •The job runs as the user who called RUN_JOB.

    •You can test a job and see any possible errors on the command line.

    •run_count, last_start_date, last_run_duration, and failure_count are not updated.

    •RUN_JOB can be run in parallel with a regularly scheduled job run.

    When use_current_session is set to FALSE:

    •The job runs as the user who is the job owner.

    •You need to check the job log to find error information.

    •run_count, last_start_date, last_run_duration, and failure_count are updated.

    •RUN_JOB fails if a regularly scheduled job is running.
    >
    You need to grant rights on the remote procedure to 'the user who is the job owner'.
  • 3. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    user a is not the owner of the package. user a can execute package directly. user a can run the job as the current session. the job was created as user a.

    I think I understand what privs are needed to run the package however I do not know where to grant the permissions - is there an oracle user that the job executes as?

    I had hoped it might execute with the permissions of the user who I created the job as - which is not the owner of the packages but which can execute the pacakages fine. However this ain't working for me.
  • 4. Re: DBMS_SCHEDULER.run_job
    rp0428 Guru
    Currently Being Moderated
    Did you read what I just posted above?
    >
    •The job runs as the user who is the job owner.
    >
    Post the DDL for the grants you are executing on the remote machine and post the DDL for the db link that you are using.
  • 5. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    Thanks for your reply, thats how I thought it worked, I guess the fact that the package is on a remote database must somehow change that behaviour?
  • 6. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    Is the job owner the person who created the job?

    When I run as the user who I created the job, the job will only successfully execute as the current session.

    The remote package does select from the local database, however it connects as the same user who created the job.

    Its a public database link. grants are the same.
  • 7. Re: DBMS_SCHEDULER.run_job
    spajdy Pro
    Currently Being Moderated
    Check dba_scheduler_job_run_details or dba_scheduler_job_log . Find log about failed run and see waht is on column additional_info. There is usually an error message.

    Edited by: spajdy on Feb 25, 2013 8:41 AM
  • 8. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    Thanks the error is showing that I cannot connect to the remote database. it seems(?) that it is not possible to use a public database link with a scheduled job as the password from a scheduled job will not match the destination schema.

    I'll have to make a private link or maybe just have a scheduled jobs in each instance.

    Thanks to everyone who gave me feedback!
  • 9. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    And that was poor advice the private link has the same issue.
  • 10. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    and that was unsuccessful - using the private links results in the same error.
  • 11. Re: DBMS_SCHEDULER.run_job
    spajdy Pro
    Currently Being Moderated
    When you are using DB link in jobs you have to specify username and password to DB link So use
    CREATE DATABASE LINK <dblink_name> CONNECT TO <username> IDENTIFIED BY <password> USING <alias>;
  • 12. Re: DBMS_SCHEDULER.run_job
    Emu Newbie
    Currently Being Moderated
    I can see in the error messages that when I am calling schema.object, what is happening is the scheduler is appending "job_owner." in front of the object.

    The result is that it attempts to execute job_owner.schemer.object which of course fails. For some reason it also gives an error message about the username/loggin on the remote database.

    If i can get permissions I will attempt to add synonyms to the remote database that point to the object in its schema...clunky. It seems to me to be better than having private database links which our DBAs may consider a security issue.

    Edited by: Emu on Feb 27, 2013 10:03 AM
  • 13. Re: DBMS_SCHEDULER.run_job
    spajdy Pro
    Currently Being Moderated
    We can't help you any more if you don't provide us you code and error you are received.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points