Find the job ID of the currently running job / allow only a single job to update a table

Question

I have a "star" database. One is doing some work; another two have jobs that pull metadata into their reference tables, from the first database. I want to stop anyone from updating, deleting or inserting any records on the reference tables in the two "slaves"; the tables should just be updated by the scheduled job.

I'm currently doing this with a trigger that checks to see if the current SID is in USER_SCHEDULER_RUNNING_JOBS with the job name I expect to be running. I'd like to change this to use the JOB_ID of the job I'm running from.

This is my current set-up; assume a really simple table:

create table a ( b number );

and the following job:

begin
   dbms_scheduler.create_job(
          job_name        => 'test_job'
        , job_type        => 'PLSQL_BLOCK'
        , job_action      => 'begin
                                 merge into a a 
                                 using ( select 1 x from dual@db2 ) b 
                                    on (1 = 2)
                                  when not matched then 
                                       insert values (b.x);
                                 commit;
                              end;'
        , start_date      => sysdate
        , repeat_interval => 'FREQ = MINUTELY;'
        , enabled => true
          );
end;
/

I'm using this trigger:

create or replace trigger tr_blah
before insert or update on a
declare
   l_ct number;
begin
   select count(*) into l_ct
     from user_scheduler_running_jobs
    where session_id = sys_context('USERENV','SID')
      and job_name = 'TEST_JOB'
          ;

   if l_ct = 0 then
      raise_application_error(-20000, 'FAIL');
   end if;
end;
/

This is inelegant; but, worse, I have to create a separate trigger for each table in each database and change the job name each time. There's no way of dynamically creating the trigger; this gets tiresome and there's a lot of scope for errors creeping in.

SYS_CONTEXT() has the parameters FG_JOB_ID and BG_JOB_ID. Their description, especially that of FG_JOB_ID implies that they might be the JOB_ID of the currently running job. Changing the trigger to the following (I've tried both):

create or replace trigger tr_a
before insert or update or delete on a
declare
   l_ct number;
begin
      raise_application_error(-20000, sys_context('USER_ENV', 'BG_JOB_ID'));
end;
/

Results in the following

ORA-20000: 
ORA-06512: at "REF.TR_A", line 4
ORA-04088: error during execution of trigger 'REF.TR_A'
ORA-06512: at line 2

This implies that both FG_JOB_ID and BG_JOB_ID are null. Is there a method of determining the ID of the job running in the current session so I don't need to use JOB_NAME each time?

Answer 1

Most elegant solution is to use different database users. Make sure the job runs under a user that has update, insert and delete grants on the tables (possible the schema owner of the tables). Don't give these grants to the other users.

No need to mess around with triggers and such.