Запуск задач в БД Oracle по расписанию, заданному регулярным выражением

Расписание запуска задач для crontab мне нравится своей простотой и наглядностью.

Можно ли в Oracle реализовать запуск задач по расписанию, похожему на расписание crontab? И при этом обойтись без программирования разбора строк, задающих расписание?

Посмотрим на пример расписания crontab. Строчки, начинающиеся с #, это комментарии, поясняющие назначение полей расписания:

# +--------------------------- minute (0 - 59)
# |   +----------------------- hour (0 - 23)
# |   |     +----------------- day of month (1 - 31)
# |   |     |     +----------- month (1 - 12)
# |   |     |     |     +----- day of week (0 - 6) (Sunday=0)
# |   |     |     |     |
# v   v     v     v     v      command to be executed
#-------------------------------------------------------------------------------
5     0     1,16  *     *      /home/ay/do_great_job.sh  > /dev/null
30    22    *     *     0      /home/ay/sunday_script.sh > /dev/null
0     0,12  *     *     0      /home/ay/hello_world      > /dev/null

Непосредственно видно, что

• в расписании имеются три задачи,
• первая запускается 1 и 16 числа каждого месяца через 5 минут после полуночи,
• вторая запускается по воскресеньям в 22:30,
• третья - по воскресеньям в полночь и в полдень.

Чтобы использовать в Oracle расписание, подобное этому, попробуем задавать расписание каждой задачи регулярным выражением, которое будет ежеминутно сопоставляться с текущим временем в формате 'mi hh24 dd mm d'. Здесь mi - минуты, hh24 - часы в формате 24 часа, dd - день месяца, mm - номер месяца, d - номер дня недели. Например,

SQL> select to_char(sysdate, 'mi hh24 dd mm d') from dual;

TO_CHAR(SYSDATE,'MIHH24DDMMD')
------------------------------
30 12 31 08 4

(Сейчас 12:30, 31 августа, среда.)

Если полученное значение '30 12 31 08 4' соответствует регулярному выражению, задающему расписание задачи, то будем запускать эту задачу на выполнение в отдельном сеансе с помощью dbms_scheduler.create_job.

Данное значение соответствует, например, таким регулярным выражениям:

.. .. .. .. .       каждую минуту
30 12 31 08 4       в 12:30, 31 августа, среду
30 12 31 08 .       в 12:30 31 августа
30 12 .. .. 4       в 12:30 каждую среду
30 12 .. .. .       в 12:30 каждый день
30 .. .. .. .       в 30 минут каждого часа
30 .. .. .. [2-6]   в 30 минут каждого часа с понедельника по пятницу
.0 .. .. .. .       каждые 10 минут

Для того, чтобы запускать задачи по расписанию на регулярных выражениях, потребуется, как минимум, следующее:

• таблица для хранения списка задач с расписаниями,
• процедура, которая просматривает этот список и запускает задачи, для которых настало время выполнения, с помощью dbms_scheduler.create_job,
• schеduler job, который запускает эту процедуру ежеминутно.

Для удобства работы со списком задач с расписаниями также нужны:

• процедура для добавления задачи с расписанием в список,
• процедура для удаления задачи с расписанием из списка,
• процедуры для изменения статуса задачи (on, off, test) в списке,
• процедура для запуска задачи вне расписания.

Статус задачи и возможность его изменения нужны для того, чтобы можно было прекратить выполнение задачи по расписанию, не удаляя ее из списка (статус off), или сообщить задаче, что она выполняется в тестовом режиме (статус test).

Итак, вот решение:

create table evt_registry (
    task_name  varchar2(30) primary key,
    descr      varchar2(4000),
    status     varchar2(5) default 'test' not null,
    what       varchar2(4000) not null,
    schedule   varchar2(100) not null,
    last_when  timestamp,
    last_job   varchar2(30),
    constraint evt_registry_status_ck check (status in ('on', 'off', 'test'))
);

comment on table evt_registry is 'Task definitions';
comment on column evt_registry.task_name is 'Task name';
comment on column evt_registry.descr is 'Description';
comment on column evt_registry.status is 'Task status, one of: on, off, test';
comment on column evt_registry.what is 'PL/SQL code to run';
comment on column evt_registry.schedule is 'Regular expression defining schedule';
comment on column evt_registry.last_when is 'When last run';
comment on column evt_registry.last_job is 'Name of the scheduler job last run';


create or replace package evt is

    g_task_status_on constant eve_registry.status%type := 'on';
    g_task_status_off constant eve_registry.status%type := 'off';
    g_task_status_test constant eve_registry.status%type := 'test';

    procedure define_task(
        p_task_name     evt_registry.task_name%type,
        p_what          evt_registry.what%type,
        p_schedule      evt_registry.schedule%type,
        p_status        evt_registry.status%type default 'TEST',
        p_descr         evt_registry.descr%type
    );
    
    -- change task status
    procedure set_task_off(p_task_name evt_registry.task_name%type);
    procedure set_task_on(p_task_name evt_registry.task_name%type);
    procedure set_task_test(p_task_name evt_registry.task_name%type);

    procedure delete_task(p_task_name evt_registry.task_name%type);

    -- run tasks as scheduled (should be run minutely)
    procedure run;

    -- run task now
    procedure run_task(p_task_name evt_registry.task_name%type);

end evt;
/

create or replace package body evt is

    procedure define_task(
        p_task_name      evt_registry.task_name%type,
        p_what          evt_registry.what%type,
        p_schedule      evt_registry.schedule%type,
        p_status        evt_registry.status%type default 'TEST',
        p_descr         evt_registry.descr%type
    ) is
    begin
        insert into evt_registry (
            task_name, descr, status, what, schedule)
        values (
            p_task_name, p_descr, p_status, p_what, p_schedule)
        ;
    end define_task;

    -- change task status
    procedure set_task_status(
        p_task_name evt_registry.task_name%type,
        p_status   evt_registry.status%type
    ) is
    begin
        update evt_registry
        set status = p_status
        where task_name = p_task_name
        ;
    end set_task_status;

    procedure set_task_off(p_task_name evt_registry.task_name%type)
    is
    begin
        set_task_status(p_task_name, g_task_status_off);
    end set_task_off;

    procedure set_task_on(p_task_name evt_registry.task_name%type)
    is
    begin
        set_task_status(p_task_name, g_task_status_on);
    end set_task_on;
    
    procedure set_task_test(p_task_name evt_registry.task_name%type)
    is
    begin
        set_task_status(p_task_name, g_task_status_test);
    end set_task_test;

    procedure delete_task(p_task_name evt_registry.task_name%type)
    is
    begin
        delete from evt_registry where task_name = p_task_name;
    end delete_task;

    -- run tasks as scheduled (should be run minutely)
    procedure run
    is
        now varchar2(100) := to_char(sysdate, 'mi hh24 dd mm d');
        l_job_name varchar2(100);
        l_plsql varchar2(4000);
    begin
        for r in (
            select task_name, what, status
            from evt_registry evt
            where regexp_like(now, schedule) 
                and status != g_task_status_off
        ) loop
            -- provide task name and status arguments if expected
            l_plsql := replace(r.what, ':1', ''''||r.task_name||'''');
            l_plsql := replace(l_plsql, ':2', ''''||r.status||'''');
            -- run task as a scheduler job
            l_job_name := dbms_scheduler.generate_job_name;
            dbms_scheduler.create_job(
                job_name => l_job_name,
                job_type => 'PLSQL_BLOCK',
                job_action => 'begin '||rtrim(l_plsql, '; ')||'; end;',
                enabled => TRUE
            );
            -- remember the job name and start time
            update evt_registry 
            set last_when = systimestamp,
                last_job = l_job_name
            where task_name = r.task_name
            ;
        end loop;
    end run;

    -- run task now
    procedure run_task(p_task_name evt_registry.task_name%type)
    is
        l_job_name varchar2(100);
        l_plsql varchar2(4000);
    begin
        for r in (
            select task_name, what, status
            from evt_registry evt
            where task_name = p_task_name
                and status != g_task_status_off
        ) loop
            -- provide task name and status arguments if expected
            l_plsql := replace(r.what, ':1', ''''||r.task_name||'''');
            l_plsql := replace(l_plsql, ':2', ''''||r.status||'''');
            -- run task as a scheduler job
            l_job_name := dbms_scheduler.generate_job_name;
            dbms_scheduler.create_job(
                job_name => l_job_name,
                job_type => 'PLSQL_BLOCK',
                job_action => 'begin '||rtrim(l_plsql, '; ')||'; end;',
                enabled => TRUE
            );
            -- remember the job name and start time
            update evt_registry 
            set last_when = systimestamp,
                last_job = l_job_name
            where task_name = r.task_name
            ;
        end loop;
    end run_task;

end evt;
/

Создам scheduler job для ежеминутного выполнения процедуры evt.run:

begin
    dbms_scheduler.create_job(
        job_name        => 'EVT_RUN',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'evt.run',
        start_date      => sysdate,
        repeat_interval => 'Freq=Minutely;Interval=1',
        end_date        => to_date(null),
        job_class       => 'DEFAULT_JOB_CLASS',
        enabled         => true,
        auto_drop       => true,
        comments        => 'Job to run EVT tasks on schedule'
    );
end;
/

И вью, чтобы видеть статус выполнения job'ов:

create or replace view evt_jobs as
select e.*, d.status job_status, d.run_duration, d.cpu_used, d.additional_info 
from user_scheduler_job_run_details d, evt_registry e
where d.job_name(+) = e.last_job
order by last_when desc;

Все готово! Создам пару тестовых задач с расписанием и проверю работоспособность нового диспетчера задач.

begin
    evt.define_task(
        p_task_name => 'T#ONE',
        p_what      => 'null',
        p_schedule  => '.. .. .. .. .',
        p_status    => 'on',
        p_descr     => 'Test task'
    );
    evt.define_task(
        p_task_name => 'T#TWO',
        p_what      => 'null',
        p_schedule  => '.(0|5) .. .. .. .',
        p_status    => 'on',
        p_descr     => 'Test task'
    );
    commit;
end;
/

Через 10 минут смотрю, как отработали задачи:

SQL> select task_name, last_when, last_job, job_status from evt_jobs;

TASK_NAME           LAST_WHEN                      LAST_JOB      JOB_STATUS
------------------- ------------------------------ ------------- ---------------
T#ONE               31-AUG-16 03.48.21.250379 PM   JOB$_117517   SUCCEEDED
T#TWO               31-AUG-16 03.45.21.230037 PM   JOB$_117516   SUCCEEDED

Теперь тестовые задачи можно удалить:

begin
    evt.delete_task('T#ONE');
    evt.delete_task('T#TWO');
    commit;
end;
/

Рассмотренный способ запуска задач по расписанию не привносит чего-то принципиально нового по сравнению с возможностями Oracle scheduler, да и не претендует на это. И все же я вижу следующие плюсы у изобретенного мной велосипеда:

• задание расписания а-ля crontab при помощи регулярного выражения; в этом случае список задач с расписаниями оказывается нагляднее и читабельнее, чем расписание Oracle scheduler;
• передача имени и статуса задачи хранимой процедуре при ее вызове (опционально и ненавязчиво); эта "бесплатная" опция позволяет гибче конфигуририровать задачи, запускаемые по расписанию.

В заключение, для полноты картины (и для контраста) приведу одинаковые расписания трех задач, заданные средствами Oracle scheduler и средствами диспетчера задач evt:

SQL> select repeat_interval, job_action from user_scheduler_jobs;

REPEAT_INTERVAL                                        JOB_ACTION
------------------------------------------------------ -------------------------
Freq=Monthly;ByMonthDay=1,16;ByHour=0;ByMinute=5       great_job
Freq=Weekly;ByDay=Sun;ByHour=22;ByMinute=30            sunday_job
Freq=Weekly;ByDay=Sun;ByHour=0,12;ByMinute=0           hello_world

SQL> select schedule, what from evt_registry;

SCHEDULE                                               WHAT
------------------------------------------------------ -------------------------
05 00 (01|16) .. .                                     great_job
30 22 .. .. 0                                          sunday_job
00 (00|12) .. .. 0                                     hello_world

---

P.S. Описанная выше функциональность реализована в пакете at_task из библиотеки atop-pl/sql, которая эксплуатируется и время от времеин обновляется. Поэтому, если хотите вопользоваться данным подходом к запуску задач по расписанию, берите пакет at_task.

Как показала практика, СУБД Oracle иногда (редко) запускает scheduler jobs с запозданием в несколько десятков секунд, из-за чего процедура run может не запустить какие-то задачи. Так, если регулярное выражение для запуска задачи T#TASK предполагает запуск задачи в 7 часов 00 минут, а Oracle Scheduler с опозданием запустил процедуру run в 7 часов 1 минуту, то T#TASK не будет запущена.

Для избежания такой ситуации следует запускать модифицированную процедуру at_task.run(p_minutes pls_integer) не ежеминутно, а реже, например, 1 раз в 3 минуты, во время, кратное 3 минутам (0, 3, 6 мин, ...), передавая процедуре аргумент 3. Тогда процедура будет запускать все задачи, запуск которых по расписанию приходится на время, кратное 3 минутам, и на две предыдущие минуты (58-0, 1-3, 4-6, ...). Таким образом, задержка запуска процедуры at_task.run(3) менее 3 минут не приведет к пропуску задач. А таких больших задержек до сих пор наблюдать не пришлось.