Skip to Main Content
  • Questions

  • scheduler: how to limit number of running jobs per user

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Julius.

Asked: May 22, 2019 - 11:48 am UTC

Last updated: May 28, 2019 - 5:30 am UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hi,

Is it possible to limit number of running scheduler jobs a user (or program) can have at any given time? I know job_queue_processes limits total number of running jobs, but I'd like to make it lower for certain database users.

Thanks,
Julius

and Connor said...

I don't think the scheduler has that natively. In later releases, we have the concept of 'resources' (can be anything you want) and you can limit jobs to not use over an allotment of resources. eg you could define each job for a user to get "10" resources, and then have a cap of "30" for that resource in total to limit concurrency to three.

Before that - two options.

1) If you just want to stop totally those jobs that are running above their concurrency limit, a profile might suffice

SQL> create profile MAX_TWO
  2  limit sessions_per_user 2;

Profile created.

SQL> alter user scott profile max_two;

User altered.

SQL> create table scott.t ( st date, en date );

Table created.

SQL>
SQL> create or replace
  2  procedure scott.p is
  3    x date := sysdate;
  4  begin
  5    dbms_session.sleep(120);
  6    insert into t values (x,sysdate);
  7    commit;
  8  end;
  9  /

Procedure created.

SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'JOB1',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'p;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true);
  8
  9      dbms_scheduler.create_job (
 10         job_name           =>  'JOB2',
 11         job_type           =>  'PLSQL_BLOCK',
 12         job_action         =>  'p;',
 13         start_date         =>  sysdate,
 14         enabled            =>  true);
 15
 16      dbms_scheduler.create_job (
 17         job_name           =>  'JOB3',
 18         job_type           =>  'PLSQL_BLOCK',
 19         job_action         =>  'p;',
 20         start_date         =>  sysdate,
 21         enabled            =>  true);
 22
 23      dbms_scheduler.create_job (
 24         job_name           =>  'JOB4',
 25         job_type           =>  'PLSQL_BLOCK',
 26         job_action         =>  'p;',
 27         start_date         =>  sysdate,
 28         enabled            =>  true);
 29
 30  end;
 31  /

PL/SQL procedure successfully completed.


Then the excessive ones will just never get to run - you'd see this in the alert log

PDB1(3):Errors in file C:\ORACLE\diag\rdbms\db18\db18\trace\db18_j001_28348.trc:
ORA-12012: error on auto execute of job "SCOTT"."JOB2"
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
2019-05-28T12:28:28.553783+08:00
PDB1(3):Errors in file C:\ORACLE\diag\rdbms\db18\db18\trace\db18_j002_25248.trc:
ORA-12012: error on auto execute of job "SCOTT"."JOB3"
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
2019-05-28T12:28:28.557711+08:00
PDB1(3):Errors in file C:\ORACLE\diag\rdbms\db18\db18\trace\db18_j003_24232.trc:
ORA-12012: error on auto execute of job "SCOTT"."JOB4"
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


2) If you want to *defer* them, then you could tweak the scheduler settings as the jobs start

The logic here is:

a job is always allowed to run *but* it will either

- do the work it was meant to do (in the example below, this will be proc P), and will remove itself the queue when done, OR

- it will see that too much is going on, and it will do nothing

In the latter case, we tell the job that it needs to repeat in 30 seconds time, to see if it can get a chance to run then, and so on

SQL> create or replace
  2  procedure scott.p(p_job varchar2) is
  3    x date := sysdate;
  4  begin
  5    insert into t values (sysdate, p_job||' started');
  6    commit;
  7    dbms_session.sleep(90);
  8    insert into t values (sysdate, p_job||' finished');
  9    commit;
 10  end;
 11  /

Procedure created.

SQL>
SQL> create or replace
  2  function allowed_to_run(p_job varchar2) return boolean is
  3    l_job_count int;
  4    l_max int;
  5  begin
  6    select count(*)
  7    into   l_job_count
  8    from   user_scheduler_running_jobs;
  9
 10    if l_job_count > 2 then
 11      dbms_scheduler.get_attribute(p_job,'max_runs', l_max);
 12      l_max := nvl(l_max,1)+1;
 13      dbms_scheduler.set_attribute(p_job,'max_runs', l_max);
 14      dbms_scheduler.set_attribute(p_job,'repeat_interval','FREQ=SECONDLY; INTERVAL=30');
 15      insert into t values (sysdate, p_job||' deferred');
 16      commit;
 17      return false;
 18    else
 19      dbms_scheduler.set_attribute_null(p_job,'repeat_interval');
 20      insert into t values (sysdate, p_job||' allowed');
 21      commit;
 22      return true;
 23    end if;
 24  end;
 25  /

Function created.

SQL>   begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'JOB1',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'if allowed_to_run(''JOB1'') then p(''JOB1''); end if;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true);
  8
  9      dbms_scheduler.create_job (
 10         job_name           =>  'JOB2',
 11         job_type           =>  'PLSQL_BLOCK',
 12         job_action         =>  'if allowed_to_run(''JOB2'') then p(''JOB2''); end if;',
 13         start_date         =>  sysdate,
 14         enabled            =>  true);
 15
 16      dbms_scheduler.create_job (
 17         job_name           =>  'JOB3',
 18         job_type           =>  'PLSQL_BLOCK',
 19         job_action         =>  'if allowed_to_run(''JOB3'') then p(''JOB3''); end if;',
 20         start_date         =>  sysdate,
 21         enabled            =>  true);
 22
 23      dbms_scheduler.create_job (
 24         job_name           =>  'JOB4',
 25         job_type           =>  'PLSQL_BLOCK',
 26         job_action         =>  'if allowed_to_run(''JOB4'') then p(''JOB4''); end if;',
 27         start_date         =>  sysdate,
 28         enabled            =>  true);
 29
 30  end;
 31  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t order by 1;

ST                  MSG
------------------- ---------------------------------------------------------------------------------
28/05/2019 13:14:21 JOB4 deferred
28/05/2019 13:14:21 JOB2 allowed
28/05/2019 13:14:21 JOB1 started
28/05/2019 13:14:21 JOB3 deferred
28/05/2019 13:14:21 JOB1 allowed
28/05/2019 13:14:21 JOB2 started

6 rows selected.

SQL> /

ST                  MSG
------------------- ---------------------------------------------------------------------------------
28/05/2019 13:14:21 JOB4 deferred
28/05/2019 13:14:21 JOB1 allowed
28/05/2019 13:14:21 JOB3 deferred
28/05/2019 13:14:21 JOB1 started
28/05/2019 13:14:21 JOB2 allowed
28/05/2019 13:14:21 JOB2 started
28/05/2019 13:14:51 JOB4 deferred
28/05/2019 13:14:51 JOB3 deferred
28/05/2019 13:15:21 JOB4 deferred
28/05/2019 13:15:21 JOB3 deferred
28/05/2019 13:15:51 JOB4 deferred
28/05/2019 13:15:51 JOB3 deferred
28/05/2019 13:15:51 JOB1 finished
28/05/2019 13:15:51 JOB2 finished

14 rows selected.

SQL> /

ST                  MSG
------------------- ---------------------------------------------------------------------------------
28/05/2019 13:14:21 JOB3 deferred
28/05/2019 13:14:21 JOB1 started
28/05/2019 13:14:21 JOB1 allowed
28/05/2019 13:14:21 JOB2 allowed
28/05/2019 13:14:21 JOB2 started
28/05/2019 13:14:21 JOB4 deferred
28/05/2019 13:14:51 JOB3 deferred
28/05/2019 13:14:51 JOB4 deferred
28/05/2019 13:15:21 JOB3 deferred
28/05/2019 13:15:21 JOB4 deferred
28/05/2019 13:15:51 JOB3 deferred
28/05/2019 13:15:51 JOB1 finished
28/05/2019 13:15:51 JOB4 deferred
28/05/2019 13:15:51 JOB2 finished
28/05/2019 13:16:21 JOB3 allowed
28/05/2019 13:16:21 JOB3 started
28/05/2019 13:16:21 JOB4 started
28/05/2019 13:16:21 JOB4 allowed
28/05/2019 13:17:51 JOB3 finished
28/05/2019 13:17:51 JOB4 finished

20 rows selected.



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database