Skip to Main Content
  • Questions

  • Query or report all runs of a scheduled job

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tony.

Asked: October 12, 2018 - 2:48 pm UTC

Last updated: October 15, 2018 - 3:58 am UTC

Version: 11g

Viewed 1000+ times

You Asked

It's possible someone has already written such a query, but how could I write a select statement that would report on all the expected times a scheduled job is going to run?

So, for example, if I have 2 DBMS_SCHEDULED jobs:
job_a runs at specific hours on the hour (freq=byhour=11,13,14)
job_b runs at specific hours on the hour (freq=byhour=9,12,15,18)

So, given the idea for this would be to quickly report which jobs are going to run say, over the next 24 hours, if the report was run just after 4pm, simple output could be something like:

12/10/2018 job_b 6pm
13/10/2018 job_b 9am
13/10/2018 job_a 11am
13/10/2018 job_b 12pm
13/10/2018 job_a 1pm
13/10/2018 job_a 2pm
13/10/2018 job_b 3pm

Then thinking further ahead, there must be a way to include other jobs where their repeat_interval is a bit more complex?

Thanks

and Connor said...

Here is something to get you started.

Take a look here

https://connor-mcdonald.com/2016/04/01/understanding-scheduler-syntax/

which shows how to use the 'evaluate_calendar_string' routine. You can fold that into something that loops through the scheduler jobs, eg

SQL> create or replace
  2  function sched return sys.odcivarchar2list pipelined is
  3    l_start_date TIMESTAMP;
  4    l_next_date TIMESTAMP;
  5    l_return_date TIMESTAMP;
  6  begin
  7    for i in (
  8      select job_name, repeat_interval, next_run_date
  9      from   dba_scheduler_jobs
 10      )
 11    loop
 12
 13      if i.repeat_interval like 'FREQ%' then
 14        l_start_date := i.next_run_date;
 15        l_return_date := l_start_date;
 16
 17
 18        for ctr in 1..10 loop
 19          dbms_scheduler.evaluate_calendar_string(
 20            i.repeat_interval,
 21            l_start_date, l_return_date, l_next_date
 22            );
 23
 24            pipe row ( to_char(l_next_date,'yyyy-mm-dd hh24:mi:ss')||'   '||i.job_name);
 25          l_return_date := l_next_date;
 26        end loop;
 27      end if;
 28    end loop;
 29    return;
 30  end;
 31  /

Function created.

SQL>
SQL> select * from sched()
  2  order by 1;

COLUMN_VALUE
-------------------------------------------------------------------------------------------------------
----------------
2018-10-14 23:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-14 23:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 00:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 00:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 01:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 01:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 02:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 02:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 03:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 03:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 04:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 04:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 05:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 05:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 05:18:06   ATE_MAINT_EVERY_1_HOUR
2018-10-15 06:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 06:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 06:18:06   ATE_MAINT_EVERY_1_HOUR
2018-10-15 07:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 07:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 07:18:06   ATE_MAINT_EVERY_1_HOUR
2018-10-15 08:17:33   CLEANUP_ONLINE_IND_BUILD
2018-10-15 08:17:43   CLEANUP_TAB_IOT_PMO
2018-10-15 08:18:06   ATE_MAINT_EVERY_1_HOUR
2018-10-15 08:18:06   ATE_MAINT_EVERY_4_HOUR
2018-10-15 09:18:06   ATE_MAINT_EVERY_1_HOUR
2018-10-15 10:18:06   ATE_MAINT_EVERY_1_HOUR
...
...


Rating

  (1 rating)

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

Comments

Exactly what we've been looking for

A reader, October 15, 2018 - 8:37 am UTC

This is exactly what we've been looking for.
Thanks!
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