Did You Know #29 – Who is Executing Jobs?

When you create a job using DBMS_SCHEDULER.CREATE_JOB you can specify a schema for the job (job_name => ‘my_schema.my_job’). What does it mean?

There are 2 relevant columns in DBA_SCHEDULER_JOBS:

  • OWNER
  • JOB_CREATOR

When we create a job under a different schema, for example we are connected as USER_A and create the job USER_B.MY_JOB, then the OWNER will be USER_B while the JOB_CREATOR will be USER_A.

The question is, what happens when the job is executed? Let’s see (this is 18c, but it behaves the same on all versions):

SQL> conn user_a/user_a@mypdb
Connected.
SQL> create table user_b.log_table (username varchar2(100));

Table created.

SQL> create procedure user_b.job_proc
  2  is
  3  begin
  4     insert into log_table values(user);
  5     commit;
  6  end;
  7  /

Procedure created.

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB (
  3     job_name             => 'USER_B.job_user',
  4     job_type             => 'STORED_PROCEDURE',
  5     job_action           => 'JOB_PROC',
  6     start_date           => sysdate,
  7     repeat_interval      => 'FREQ=MINUTELY',
  8     enabled              =>  TRUE);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> select OWNER,JOB_NAME,JOB_CREATOR,LAST_START_DATE,NEXT_RUN_DATE
  2  from dba_scheduler_jobs
  3  where job_name='JOB_USER';

OWNER                JOB_NAME             JOB_CREATOR          LAST_START_DATE                        NEXT_RUN_DATE
-------------------- -------------------- -------------------- -------------------------------------- --------------------------------------
USER_B               JOB_USER             USER_A               28-FEB-19 09.37.24.771571 PM -08:00    28-FEB-19 09.38.24.808658 PM -08:00

SQL> select * from user_b.log_table;

USERNAME
----------------------------------------------------------------------------------------------------
USER_A
USER_A

As you can see, the job is executed by USER_A, the creator (we see that the user that was inserted into the table is USER_A), however the schema is USER_B (as the LOG_TABLE belongs to USER_B and rows were inserted into it without the schema prefix).

5 thoughts on “Did You Know #29 – Who is Executing Jobs?”

  1. Hi Liron,
    This behavior contradicts what is explicitly stated in the Administrator Guide, by which “a local database job always runs as the job owner” (also ignoring any credential that might have been specified).

    However, this behavior is consistent with the behavior of the older DBMS_JOB.
    Specifically, if you have a procedure owned by USER_A that submits a job using DBMS_JOB.SUBMIT,
    and that procedure is executed by USER_B, then the owner of the job is USER_A, but the job itself is executed
    as USER_B.
    Only USER_A will see the job in his USER_JOBS, but USER_B appears in DBA_JOBS.LOG_USER.

    Cheers & Best Regards,
    Iudith Mentzel

    Like

    1. Hi Iudith,
      Could you shared the link to the admin guide where you saw this contradiction? I looked for some information about it before writing the post but couldn’t find it.
      Thanks for the comment

      Like

      1. Unfortunately, as an “outsider”, I don’t have access to MOS 😦

        It would be great if Oracle would open up MOS to everybody who has an Oracle username,
        for read-only access to knowledge base, white papers, a.s.o.
        But they not seem to be doing this in the close future 😦

        Cheers & Best Regards,
        Iudith

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s