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).