JOBS

1. Создаём программы

2. Создаём расписания

3. Создаём задания для запуска программы в соответствии с расписанием

Программы создаются так:

BEGIN
  -- PL/SQL Block.
  DBMS_SCHEDULER.create_program (
    program_name   => 'test_plsql_block_prog',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    enabled        => TRUE,
    comments       => 'Program to gather SCOTT''s statistics using a PL/SQL block.');

  -- Shell Script.
  DBMS_SCHEDULER.create_program (
    program_name        => 'test_executable_prog',
    program_type        => 'EXECUTABLE',
    program_action      => '/u01/app/oracle/dba/gather_scott_stats.sh',
    number_of_arguments => 0,
    enabled             => TRUE,
    comments            => 'Program to gather SCOTT''s statistics us a shell script.');

 

  -- Stored Procedure with Arguments.
  DBMS_SCHEDULER.create_program (
    program_name        => 'test_stored_procedure_prog',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'DBMS_STATS.gather_schema_stats',
    number_of_arguments => 1,
    enabled             => FALSE,
    comments            => 'Program to gather SCOTT''s statistics using a stored procedure.');

  DBMS_SCHEDULER.define_program_argument (
    program_name      => 'test_stored_procedure_prog',
    argument_name     => 'ownname',
    argument_position => 1,
    argument_type     => 'VARCHAR2',
    default_value     => 'SCOTT');

  DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog');
END;
/

PL/SQL procedure successfully completed.

 

Просмотр программ:
 

SELECT owner, program_name, enabled FROM dba_scheduler_programs;

OWNER                          PROGRAM_NAME                   ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG_PROG                 TRUE
SYS                            GATHER_STATS_PROG              TRUE
SYS                            TEST_PLSQL_BLOCK_PROG          TRUE
SYS                            TEST_EXECUTABLE_PROG           TRUE
SYS                            TEST_STORED_PROCEDURE_PROG     TRUE

5 rows selected.


Удалить программы: 

BEGIN
  DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog');
  DBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog');
  DBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog');
END;
/

PL/SQL procedure successfully completed.

 

Просмотр программ:

 
SELECT owner, program_name, enabled FROM dba_scheduler_programs;

OWNER                          PROGRAM_NAME                   ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG_PROG                 TRUE
SYS                            GATHER_STATS_PROG              TRUE 

Расписания создаются так:

BEGIN DBMS_SCHEDULER.create_schedule (

    schedule_name   => 'test_hourly_schedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    comments        => 'Repeats hourly, on the hour, for ever.');
END;
/

PL/SQL procedure successfully completed.

 

Просмотр расписаний:

SELECT owner, schedule_name FROM dba_scheduler_schedules;

OWNER                          SCHEDULE_NAME
------------------------------ ------------------------------
SYS                            DAILY_PURGE_SCHEDULE
SYS                            TEST_HOURLY_SCHEDULE



Удалить расписание:
BEGIN
  DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_HOURLY_SCHEDULE');
END;
/

PL/SQL procedure successfully completed.

 

Просмотр расписаний:

 
SELECT owner, schedule_name FROM dba_scheduler_schedules;

OWNER                          SCHEDULE_NAME
------------------------------ ------------------------------
SYS                            DAILY_PURGE_SCHEDULE

1 row selected. 

Задания (джобы) создаются так:

 BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

  -- Job defined by an existing program and schedule.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_job_definition',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule.');

  -- Job defined by existing program and inline schedule.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_prog_job_definition',
    program_name    => 'test_plsql_block_prog',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined by existing program and inline schedule.');

  -- Job defined by existing schedule and inline program.
  DBMS_SCHEDULER.create_job (
     job_name      => 'test_sched_job_definition',
     schedule_name => 'test_hourly_schedule',
     job_type      => 'PLSQL_BLOCK',
     job_action    => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
     enabled       => TRUE,
     comments      => 'Job defined by existing schedule and inline program.');
END;
/

PL/SQL procedure successfully completed.

 

Просмотр заданий (jobs):
 

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      TRUE
SYS                            GATHER_STATS_JOB               TRUE
SYS                            TEST_FULL_JOB_DEFINITION       TRUE
SYS                            TEST_PROG_SCHED_JOB_DEFINITION TRUE
SYS                            TEST_PROG_JOB_DEFINITION       TRUE
SYS                            TEST_SCHED_JOB_DEFINITION      TRUE

Джобы обычно выполняются асинхронно под контролем job coordinator,
но их можно запускать и останавливать вручную с помощью процедур RUN_JOB и STOP_JOB. 

BEGIN
  -- Запустить джоб в синхронном режиме.
  DBMS_SCHEDULER.run_job (job_name            => 'test_full_job_definition',
                          use_current_session => TRUE);

  -- Остановить джоб.
  DBMS_SCHEDULER.stop_job (job_name => 'test_full_job_definition, test_prog_sched_job_definition');
END;
/


Джобы можно удалить так:

BEGIN
  DBMS_SCHEDULER.drop_job (job_name => 'test_full_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_sched_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_prog_job_definition');
  DBMS_SCHEDULER.drop_job (job_name => 'test_sched_job_definition');
END;
/

PL/SQL procedure successfully completed.


Просмотр джобов:

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       ENABL
------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      TRUE
SYS                            GATHER_STATS_JOB               TRUE

2 rows selected.


 

Классы джобов:

Классы джобов позволяют группировать задания со схожими характеристиками и требованиями к ресурсам, 
что облегчает администрирование.
Если параметр JOB_CLASS процедуры CREATE_JOB не определен, задание присваивается DEFAULT_JOB_CLASS.


-- Просмотр текущих resource consumer groups.

SELECT consumer_group FROM dba_rsrc_consumer_groups;
CONSUMER_GROUP
------------------------------
OTHER_GROUPS
DEFAULT_CONSUMER_GROUP
SYS_GROUP
LOW_GROUP
AUTO_TASK_CONSUMER_GROUP

5 rows selected.


-- Создание джоб класса:
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name          =>  'test_job_class',
    resource_consumer_group =>  'low_group');
END;
/

PL/SQL procedure successfully completed.



-- Просмотр классов джобов.

SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes;

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP
TEST_JOB_CLASS                 LOW_GROUP

3 rows selected.



Задания могут быть назначены job class-у либо во время создания джоба
или после создания джоба с использованием процедуры SET_ATTRIBUTE.

BEGIN
  -- Создаём джоб.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_class_job_def',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    job_class     => 'test_job_class',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule and assigned toa job class.');

  -- Назначаем джоб классу.
  DBMS_SCHEDULER.set_attribute (
    name      => 'test_prog_sched_job_definition',
    attribute => 'job_class',
    value     => 'test_job_class');
END;
/

PL/SQL procedure successfully completed.


-- Просмотр джобов.
SELECT owner, job_name, job_class, enabled FROM dba_scheduler_jobs;

OWNER                          JOB_NAME                       JOB_CLASS                      ENABL
------------------------------ ------------------------------ ------------------------------ -----
SYS                            PURGE_LOG                      DEFAULT_JOB_CLASS              TRUE
SYS                            GATHER_STATS_JOB               AUTO_TASKS_JOB_CLASS           TRUE
SYS                            TEST_FULL_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_PROG_SCHED_JOB_DEFINITION TEST_JOB_CLASS                 TRUE
SYS                            TEST_PROG_JOB_DEFINITION       DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_SCHED_JOB_DEFINITION      DEFAULT_JOB_CLASS              TRUE
SYS                            TEST_PROG_SCHED_CLASS_JOB_DEF  TEST_JOB_CLASS                 TRUE

7 rows selected.


Удаление джоб классов:

BEGIN
  DBMS_SCHEDULER.drop_job_class (
    job_class_name => 'test_job_class',
    force          => TRUE);
END;
/

PL/SQL procedure successfully completed.

Параметр force отключает любые зависимые джлбы и устанавливает для их job class значение по умолчанию.
Если job class не имеет зависимостей, параметр force необязателен. 

-- Просмотр джоб классов.

SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes;

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP
------------------------------ ------------------------------
DEFAULT_JOB_CLASS
AUTO_TASKS_JOB_CLASS           AUTO_TASK_CONSUMER_GROUP

2 rows selected.
 

Заданию могут назначаться приоритет между 1 и 5 (причем 1 является самым высоким),
которые использует координатор очереди джобов. Если приоритет не назначен, используется приоритет 3.

BEING
  DBMS_SCHEDULER.set_attribute (
    name      => 'my_job', 
    attribute => 'job_priority',
    value     => 1);
END;
/