如何在窗口中安排 Oracle DBMS 作业

2021-12-24 00:00:00 oracle11g oracle plsql dbms-scheduler

我想创建一个每周日(不是周末)从 09:00 到 20:00 每 10 分钟运行的 Oracle DBMS 作业.我想知道我是否可以在作业定义的 FREQ 参数中做到这一点,或者我必须创建一个 New Maintenance Window.

I want to create an Oracle DBMS Job that runs every week day (not on weekends) from 09:00 to 20:00 every 10 min. I wonder if I can do that in the FREQ parameter of the job definition or I have to create a New Maintenance Window.

似乎在提出的解决方案中,作业仅在 9 和 20 运行,并且在第一次执行后,当我运行此查询时

It seems that with the solution proposed, the job runs at 9 and 20 only, and after first execution, when I run this query

select owner, job_name, next_run_date 
from dba_scheduler_jobs 
where JOB_NAME = 'GET_INVOICES_JOB';

我收到了 09/10/17 20:01:27,000000000 欧洲/马德里

'freq=minutely; interval=10; byhour=9,20; byday=MON,TUE,WED,THU,FRI; exclude=Company_Holidays; bysetpos=-1'

推荐答案

你可以使用这个:

begin
dbms_scheduler.create_job (
   job_name           =>  'jb_en_lopes',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'pr_en_lopes',
   start_date         =>  '09-oct-2017 09:00:00 am',
   repeat_interval    =>  'freq=minutely; interval=10; byhour=9,10,11,12,13,14,15,16,17,18,19,20; byday=MON,TUE,WED,THU,FRI;',
   enabled            =>  true);
end;

当这个调度器负责时,我得到以下结果:

When this scheduler in charge I get the results below :

select * 
  from dba_scheduler_job_log l
 where l.job_name = 'JB_EN_LOPES'
 order by l.log_date desc;

 LOG_ID LOG_DATE                            OPERATION   STATUS

1051594 10-OCT-17 09.59.01.197420 AM +03:00    RUN      SUCCEEDED  
1051592 10-OCT-17 09.58.02.229724 AM +03:00    RUN      SUCCEEDED  
1051590 10-OCT-17 09.57.03.177907 AM +03:00    RUN      SUCCEEDED  
1051588 10-OCT-17 09.56.01.197341 AM +03:00    RUN      SUCCEEDED

哪里:

select owner, job_name, next_run_date                                  
  from dba_scheduler_jobs                                               
 where JOB_NAME = 'JB_EN_LOPES'; 

 OWNER    JOB_NAME       NEXT_RUN_DATE

 myschema JB_EN_LOPES   10-OCT-17 08.00.00.194958 PM +03:00

更新:

如果您无权访问 dba_ 视图,请考虑将这些前缀替换为 user_,并从选择列表中删除 owner 列对于最后一个查询

If you have no access to dba_ views, then consider to replace those prefixes with user_, and remove owner column from the select list for the last query as

select job_name, next_run_date                                  
  from user_scheduler_jobs                                               
 where JOB_NAME = 'JB_EN_LOPES'; 

相关文章