Стандартные пакеты. DBMS_JOB

dbstalker, 19 июня

Мне кажется, что этот пакет самый часто используемый администраторами. А предназначен он для планирования фоновых заданий в базе данных. Большая часть заданий по сопровождению баз данных должна проводиться в период минимальной активности пользователей, то есть в нерабочее время. Организовать эту работу в таком режиме можно, используя этот пакет.

Каким может быть задание? Это может быть и блок PL/SQL, и хранимая процедура, и внешняя процедура на C или JAVA.

Задание выполняется в фоновом режиме.Не забудьте выставить параметр инициализации JOB_QUEUE_PROCESSES.

Частота выполнения задается или как однократное задание или регулярное.

Важное свойство пакета – задание попадает в очередь только после фиксации транзакции. То есть, если задание включено в транзакцию, а транзакция подверглась откату, то задание выполняться не будет.

Управление фоновыми заданиями реализуется отдельными серверными процессами (SNP), которые должны быть запущены, прежде чем будут активизированы задания. Эти процессы активизируются с установленной периодичностью, просматривают очередь и выполняют те задания, у которых настало время активизации. Одновременно может работать до 10 процессов SNP, которые являются неотъемлемой частью нашего экземпляра.

Поэтому сначала необходимо установить количество фоновых процессов для выполнения наших заданий. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=NN, где NN – желаемое количество процессов (допустимо от 0 до 10). Каким должно быть NN? Это число зависит от интенсивности использования пакета. Если у вас много снапшотов, различных заданий, то может потребоваться увеличение NN.

Еще один параметр инициализации JOB_QUEUE_INTERVAL интервал активизации фоновых процессов в секундах. Допустимо от 1 до 3600.

Описание пакета

Рассмотрим теперь основные процедуры пакета DBMS_JOB.

SUBMIT, ISUBMIT – процедуры, которые посылают задание на выполнение. Аргументы процедуры:

job       OUT BINARY_INTEGER,( job IN  BINARY_INTEGER для ISUBMIT),
what      IN  VARCHAR2,
next_date IN  DATE DEFAULT sysdate,
interval  IN  VARCHAR2 DEFAULT 'null',
no_parse  IN  BOOLEAN DEFAULT FALSE,
instance  IN  BINARY_INTEGER DEFAULT 0,
force     IN  BOOLEAN DEFAULT FALSE

job - это идентификатор задания. Является выходным аргументом для SUBMIT, и входным для ISUBMIT. Его используют, когда нужно получить информацию о конкретном заданий ( через представления user_jobs или dba_jobs) или произвести определенные манипуляции заданием.

What - а этот аргумент как раз и определяет, что конкретно нужно сделать. Параметр передается в форме символьной строки в одинарных кавычках, заканчивающейся точкой с запятой.

next_date – время следующего выполнения. Если мы только создаем задание, то это время первого выполнения. По умолчанию –sysdate. В дальнейшем этот параметр вычисляется в соответствии со значением параметра Interval и даты и времени фактического выполнения.

Interval - сюда мы должны поместить функцию с помощью которой будет задан временной интервал для задания, которое будет выполнятся регулярно. Если выражение параметра принимает значение NULL, после выполнения задание удаляется из очереди.

no_parse - по умолчанию - FALSE, это значит параметр what будет проверятся на выполняемость. Если TRUE – WHAT не проверяется на допустимость.

instance – указывается экземпляр, на котором будет выполняться задание ( только для Parallel server).

Force – если False: задание завершается неудачно, если указанный экземпляр недоступен. (только для Parallel server).

Задание посылается на выполнение пользователем, идентификатор пользователя ассоциируется с эти заданием. Дальнейшая работа с этим заданием (удаление, изменение) возможна только этим пользователем.

REMOVE – процедура для удаления задания из очереди. Единственный входной параметр - job IN BINARY_INTEGER. Его значение вы всегда можете узнать из вьюва all_jobs или dba_jobs. Обратите внимание, задание удаляется из очереди. То есть задание снова не будет выполняться. Если же оно выполняется, то remove его не прекратит.

RUN – процедура для немедленного выполнения задания в пользовательском сеансе (не в фоновом режиме). Так же нужен только один параметр job IN BINARY_INTEGER. Нужно также учитывать, что после вызова этой процедуры будет переустановлена дата следующего выполнения задания по параметру interval.

Change – процедура для изменения любого параметра задания, которое находится в очереди. Входные параметры :

job       IN  BINARY_INTEGER,
what      IN  VARCHAR2,
next_date IN  DATE,
interval  IN  VARCHAR2,
instance  IN  BINARY_INTEGER DEFAULT NULL,
force     IN  BOOLEAN DEFAULT FALSE

Номер задания указывать нужно обязательно. Вместо остальных , которые изменять не нужно, можно прописать NULL.

  PROCEDURE what      ( job       IN  BINARY_INTEGER,
                        what      IN  VARCHAR2 );

  PROCEDURE next_date ( job       IN  BINARY_INTEGER,
                        next_date IN  DATE     );
  PROCEDURE interval  ( job       IN  BINARY_INTEGER,
                        interval  IN  VARCHAR2 );

Эти процедуры также изменяют задание, но только соответствующий параметр.

BROKEN – процедура для восстановления или разрушения заданий ( активное и неактивное состояние).Параметры:

        job       IN  BINARY_INTEGER,
         broken    IN  BOOLEAN,
         next_date IN  DATE DEFAULT SYSDATE

broken=yes – задание разрушено и не выполняется.Задание, которое 16 раз подряд не могло успешно выполнится переводится автоматически в состояние «разрушенное» и выполняться уже не будет.

Приведу пример создания задания на сбор статистики

declare
 jn number;
begin
 dbms_job.submit(jn,
'begin
  for c in(select owner, table_name 
           from all_tables 
           where owner in (''AIS'',''SCOTT'',''TEST'',''MY_MY'',''ETALON'')
           minus
           select owner, table_name from all_external_tables) loop
    dbms_stats.gather_table_stats(ownname=>c.owner,tabname=>c.table_name, cascade=>true, degree=>1);
    commit;
  end loop;         
end;',
trunc(sysdate+1)+3/24,'trunc(sysdate+1)+3/24');
commit;
end;
/

Ничего сложного нет, правда?

Регулярное выполнение заданий.

Преимущества использования очередей заданий перед использованием утилит операционной системы (cron, at) состоит в том, что нет проблемы защиты пароля, задание выполняется только при открытой базе данных, в случае сбоя сервера задание будет пытаться повторно выполняться. Задание пробует успешно выполниться 16 раз и только после помечается как разрушенное (broken=yes), попытки выполнить это задание прекращаются. И еще одно большое преимущество - наличие достаточно полной информации о процессе выполнения заданий (представления all_jobs, dba_jobs, dba_jobs_running).

Для создания регулярного задания очень важен параметр interval. Обычно это функция , которая работает с датой. Например, если указать этот параметр равным trunc(sysdate)+1+2/24, то функция всегда возвращает 2 часа ночи следующих суток. Если interval задавать именно по такой аналогии (фиксированный момент времени), то удается избежать смещения заданий, которое весьма вероятно возникнет, если этот параметр будет задан в относительной форме ( например, sysdate+1). Как в таком случае возникает смещение?

Задания выполняются последовательно в соответствии с заданным временем выполнения. Если у вас один процесс обработки очереди (смотрите параметр JOB_QUEUE_PROCESSES в init.ora) , а в очереди у вас несколько заданий на одно и тоже время, то очевидно задания будут выполняться последовательно, с некоторым смещение относительно заданного времени. И к тому же очередь просматривается периодически, например 30 сек. Исходя из всего этого очевидно, что если задавать относительный момент времени (например, sysdate+1), то получим медленное смещение времени выполнения регулярного задания. Поэтому, если важно точно выполнять задание в конкретный момент времени, то используйте функцию, которая всегда возвращает фиксированный момент времени (например, trunc(sysdate)+1+2/24)

Контроль над выполнением заданий.

Есть три чудесных представления для того чтобы не оставлять без присмотра ваши задания.

DBA_JOBS – полный список заданий, которые стоят в очереди в нашей базе данных

USER_JOBS – это представление видно всем юзерам как all_users. Это список заданий созданных текущим юзером.

DBA_JOBS_RUNNING – список заданий, которые в данный момент времени выполняются.

А теперь про информацию, которую можно почерпать из этих вьювов.

LAST_DATE,LAST_SEC – дата и время последнего выполнения задания

THIS_DATE, THIS_SEC - если задание выполняется в данное время, то это начало выполнения задания

NEXT_DATE, NEXT_SEC - дата и время следующего выполнения

TOTAL_TIME – время выполнения задания (накопительные данные)

BROKEN – если принимает значение yes, то задание разрушено и выполняться не будет. No – задание не разрушено. Установить нужное значение можно процедурой dbms_job.broken . К тому же, если было 16 неудачных попыток выполнить задание, то оно автоматически разрушается.

INTERVAL – функция , которая возвращает дату следующего выполнения задания.

FAILURES – количество неудачных попыток выполнения задания (подряд). Если равно 0, то задание выполняется успешно.

WHAT – текст самого задания

NLS_ENV – среда выполнения задания, наследуется из среды, из которой задание сформировано

INSTANCE – это идентификатор экземпляра (для Parallel server).

Сообщения о неуспешных попытках выполнить задание (причинах неуспешности) можно найти в alert.log, где обычно указывается файл трассировки.

26 комментариев

Прокоментировать

Андрей
21 апреля 2008 г. в 11:56

статья очень интересная. Спасибо!

dbstalker
21 апреля 2008 г. в 12:52

Приятно, что Вам понравилась статья. Будем рады, если она пригодится Вам в работе.

Андрей
21 апреля 2008 г. в 11:58

Скажите, DBMS_SCHEDULER есть в Oracle 9 версии?

dbstalker
21 апреля 2008 г. в 12:48

Увы, пакет DBMS_SCHEDULER входит в состав Oracle Database 10g. В состав Oracle Database 9i входит пакет DBMS_JOB.

Anonymous
23 апреля 2008 г. в 17:17

Добрый день. Я формирую файл пакетом UTL_FILE. Как мне его перенести с сервера на клиентскую машину средствами сервера Oracle?

dbstalker
24 апреля 2008 г. в 09:55

Самый простой способ:на клиенте создайте папку, сделайте доступ к ней (для начала, всем - всё).Затем создайте объект DIRECTORY на сервере :create or replace directory LOAD_DIR as '\\сетевое_имя_клиента\шара\'; Пакетом UTL_FILE прямо в эту directory формируйте файл. Если у Вас Oracle Database 10g, то вы можете работать с пакетом DBMS_SCHEDULER, который может выполнять команды ОС. Успехов!

Anonymous
25 апреля 2008 г. в 13:15

Спасибо за статью, помогла решить проблему с отсутствием DBMS_SCHEDULER в Oracle9

Новичек
27 мая 2008 г. в 13:59

Был приведен пример как поставить в интервале 2 часа ночи "trunc(sysdate)+1+2/24", а как можно поставить минуты, например 2:30 ночи. Подскажите если можно))

dbstalker
27 мая 2008 г. в 17:11

Например, trunc(sysdate+1)+2.5/24

Максим
15 сентября 2008 г. в 12:33

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

dbstalker
16 сентября 2008 г. в 09:36

Если речь идет о том, чтобы следующий запуск задания происходил только лишь после того, как завершится текущий запуск этого же задания, то так в оракле и происходит. Если же речь идет о последовательном выполнении различных заданий, то можно попробовать выставить параметр JOB_QUEUE_PROCESSES в значение равное 1. В этом случае запустится один фоновый процесс. Скорее всего этот процесс может работать только с одним задание. Пробуйте. Очень надеюсь,что Вы нам сообщите о результатах изысканий. Успехов!

geo
1 декабря 2008 г. в 13:29

A kak parametr interval preobrazovatj v datu (chasy minuty,sekundy), inache eto pole varchar2.

dbstalker
1 декабря 2008 г. в 14:56

Это поле interval и должно быть типа varchar2. А для чего его преобразовывать в тип дата?

Игорь
18 января 2009 г. в 09:08

Можно ли при помощи DBMS_JOB.SUBMIT запустить на параллельное выполнение одну и ту же хранимую процедуру, но с разными значениями параметров?

dbstalker
19 января 2009 г. в 09:13

Конечно можно. При условии, что у вас параметр JOB_QUEUE_PROCESSES установлен в значение достаточное для одновременного запуска нескольких заданий.Наверняка долженбыть больше 1.

Pupkin
19 февраля 2009 г. в 09:32

Спасибо, статья очень полезная, как и большинство на вашем сайте.
Скажите, а как сделать чтобы задание повторялось скажем каждые 30 минут?

dbstalker
19 февраля 2009 г. в 09:47

параметр INTERVAL установите в значение sysdate + 1/48

Victor
4 сентября 2009 г. в 15:00

Есть несколько процедур - последовательно мержатся таблицы с удаленного сервера, но важно что бы они выполнядись в очередной последовательности - как это можно сделать? т.е. реализовать логику выполнения заданий.

dbstalker
8 сентября 2009 г. в 10:55

Напишите скрипт, где организуете последовательное выполнение этих процедур+ обработку ошибок. Скрипт запускайте на выполнение джобом. Самый простой вариант.

Саша
12 ноября 2010 г. в 20:15

Всё бы ничего, да если джоб должен создаваться с разными параметрами - отсутствие биндинга этих параметров есть повышенный источник проблем.

Newby
26 февраля 2011 г. в 17:20

Скажите, пожалуйста, как узнать, сколько времени отрабатывал джоб ? Правильно ли я понял, что это будет ( LAST_DATE,LAST_SEC - время запуска ) ?

dbstalker
28 февраля 2011 г. в 11:06

last_date - дата старта последнего раза удачной работы джоба.
total_time - общее время работы джоба со времени самого первого задания

Newby
28 февраля 2011 г. в 18:31

total_time после 2го запуска джоба - total_time после первого запуска = время выполнения джоба в секундах

svik
30 июля 2011 г. в 11:11

что делать, если случайно удалили записи из таблицы sys.job$. Как можно восстановить эту таблицу?

dbstalker
1 августа 2011 г. в 12:23

Создайте удаленное задание вновь

inko
24 января 2013 г. в 21:01

Спасибо,очень познавательно и просто написано. Я уже 3ю вашу статью читаю связанную с пакетами.

 

Новый комментарий

Я не спамер: введите суму 5+3



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Нужен поставщик Дропшиппинг
10 декабря, 1 ответа
КИНО КАФЕ!!!!!!!!!!!!!!!!
10 декабря, 1 ответа