问题描述
嗨，
是否可以限制用户 (或程序) 在任何给定时间可以拥有的运行调度程序作业的数量？我知道job_queue_processes限制了正在运行的作业的总数，但我想降低某些数据库用户的数量。
谢谢，
朱利叶斯
是否可以限制用户 (或程序) 在任何给定时间可以拥有的运行调度程序作业的数量？我知道job_queue_processes限制了正在运行的作业的总数，但我想降低某些数据库用户的数量。
谢谢，
朱利叶斯
专家解答
我不认为调度程序本身就有。在以后的版本中，我们有了 “资源” 的概念 (可以是您想要的任何东西)，并且您可以将作业限制为不使用资源分配。例如，您可以为用户定义每个作业以获取 “10” 资源，然后为该资源设置总计 “30” 的上限，以将并发限制为3。
在此之前-两个选择。
1) 如果您只想完全停止运行超过其并发限制的那些作业，则配置文件可能就足够了
那么过多的将永远无法运行-您会在警报日志中看到这一点
2) 如果要 * 推迟 * 它们，则可以在作业开始时调整调度程序设置
这里的逻辑是:
总是允许作业运行 * 但是 * 它将
-完成原本打算做的工作 (在下面的示例中，这将是proc P)，并在完成后将自己删除队列，或者
-它会看到发生的事情太多了，它什么也做不了
在后一种情况下，我们告诉作业它需要在30秒内重复，看看它是否有机会运行，依此类推
在此之前-两个选择。
1) 如果您只想完全停止运行超过其并发限制的那些作业，则配置文件可能就足够了
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.
那么过多的将永远无法运行-您会在警报日志中看到这一点
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) 如果要 * 推迟 * 它们，则可以在作业开始时调整调度程序设置
这里的逻辑是:
总是允许作业运行 * 但是 * 它将
-完成原本打算做的工作 (在下面的示例中，这将是proc P)，并在完成后将自己删除队列，或者
-它会看到发生的事情太多了，它什么也做不了
在后一种情况下，我们告诉作业它需要在30秒内重复，看看它是否有机会运行，依此类推
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.
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
