В Oracle9i Release 1 появился новый тип триггеров, запускаемых в ответ на приостановление выполняемых команд. Приостановление может быть вызвано разными причинами — например, превышением квоты выделенного процессу табличного пространства. Триггер AFTER SUSPEND должен решить проблему, чтобы выполнение команды было возобновлено. Это просто подарок для разработчиков, которым надоели проблемы с нехваткой памяти, и для администраторов базы данных, которым приходится эти проблемы решать. Синтаксис создания триггера AFTER SUSPEND сходен с синтаксисом создания триггеров PL/SQL для DDL и триггеров событий базы данных Oracle. В нем объявляется инициирующее событие (suspend), время запуска (after) и область действия (database или schema):
1 CREATE [OR REPLACE] TRIGGER имя_триггера
2 AFTER SUSPEND
3 ON {DATABASE | SCHEMA}
4 BEGIN
5 ... код ...
6 END;В знакомстве с AFTER SUSPEND нам поможет пример типичной ситуации, требующей создания этого вида триггеров.
Допустим, некоему разработчику приходится сопровождать сотни программ, которые выполняются в течение ночи, выполняют продолжительные транзакции для обобщения информации и ее перемещения между разными приложениями. По крайней мере дважды в неделю разработчик получает ночью сообщения на пейджер, потому что в одной из его программ возникла ошибка Oracle:
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
Следующий неизбежный шаг — звонить администратору базы данных и просить увеличить пространственную квоту. Администратор спрашивает: «А сколько нужно?», на что разработчик может ответить лишь неубедительным: «Даже не знаю, объем данных сильно изменяется». Оба остаются весьма недовольными, потому что администратору нужен контроль за выделяемым пространством в целях планирования, а разработчику не хочется так часто просыпаться от служебных сообщений.
Настройка для триггера AFTER SUSPEND
К счастью, триггер AFTER SUSPEND может решить проблемы обоих героев. Посмотрим, как же решается эта задача. Разработчик обнаруживает конкретную точку в коде, в которой чаще всего встречается ошибка. Это внешне непримечательная команда INSERT в конце программы, выполняющейся несколько часов:
INSERT INTO monthly_summary (
acct_no, trx_count, total_in, total_out)
VALUES (
v_acct, v_trx_count, v_total_in, v_total_out);
Больше всего бесит то, что на вычисления уходит несколько часов, а вычисленные значения немедленно теряются при сбое завершающей команды INSERT. По меньшей мере разработчик хочет, чтобы программа приостанавливалась, пока он обращается к администратору за дополнительным пространством. Он обнаруживает, что это можно сделать простой командой ALTER SESSION:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Monthly Summary';Это означает, что каждый раз, когда сеанс Oracle обнаруживает ошибку нехватки пространства, он переходит в приостановленное состояние на 3600 секунд (1 час) — если повезет, то с возможностью продолжения. Это дает необходимое время, чтобы система мониторинга могла отправить сообщение, разработчик обратился к администратору, а тот выделил дополнительное пространство. Система не идеальная, но по крайней мере часы, потраченные за вычисления, не будут теряться понапрасну.
Другая проблема заключается в том, что при попытках разрешить ситуацию в середине ночи и разработчик и администратор чувствуют такую усталость и раздражительность, что на разрешение недоразумений уходит лишнее время. На помощь приходит другая особенность приостановленных команд: представление DBA_RESUMABLE. В нем содержится информация обо всех сеансах, зарегистрированных для продолжения приведенной командой ALTER SESSION.
Чтобы пользователи могли включить режим приостановки, им должна быть предоставлена системная привилегия RESUMABLE.
Теперь когда программа переходит в приостановленное состояние, разработчику остается лишь позвонить администратору и пробормотать в трубку: «Проверь представление dba_resumable». Администратор запрашивает информацию со своей учетной записи и смотрит, что же происходит:
SQL> SELECT session_id,
2 name,
3 status,
4 error_number
5 FROM dba resumable
SESSION_ID NAME STATUS ERROR_NUMBER
-------------- ----------------------- ------ -----------------
8 Monthly Summary SUSPENDED 1536
1 row selected.
Из результатов видно, что сеанс 8 приостановлен из-за ошибки ORA-01536 (превышение пространственной квоты). По прошлому опыту администратор знает, какая схема и табличное пространство виноваты в случившемся, он устраняет проблему и бормочет в трубку: «Готово». Приостановленная команда в коде разработчика немедленно возобновляет выполнение, а разработчик и администратор могут спать спокойно.
Код триггера
Через несколько недель и разработчику, и администратору надоедают повторяющиеся (хотя и с меньшей частотой) ночные переговоры, и администратор решает автоматизировать свою работу при помощи триггера AFTER SUSPEND. Фрагмент кода, который он пишет и устанавливает под административной учетной записью:
TRIGGER after_suspend
AFTER SUSPEND
ON DATABASE
DECLARE
...
BEGIN
-- Если это ошибка, связанная с доступным пространством...
IF ORA_SPACE_ERROR_INFO ( error_type => v_error_type,
object_type => v_object_type,
object_owner => v_object_owner,
table_space_name => v_tbspc_name,
object_name => v_object_name,
sub_object_name => v_subobject_name ) THEN
-- Если произошло превышение квоты табличного пространства...
IF v_error_type = 'SPACE QUOTA EXCEEDED' AND
v_object_type = 'TABLE SPACE' THEN
-- Получение имени пользователя
OPEN curs_get_username;
FETCH curs_get_username INTO v_username;
CLOSE curs_get_username;
-- Получение текущей квоты для имени пользователя и табличного пространства
OPEN curs_get_ts_quota(v_object_name,v_username);
FETCH curs_get_ts_quota INTO v_old_quota;
CLOSE curs_get_ts_quota;
-- Создание команды ALTER USER и ее отправка заданию
-- (потому что при попытке выполнить ее "на месте"
-- произойдет ошибка ORA-30511)
v_new_quota := v_old_quota + 40960;
v_sql := 'ALTER USER ' || v_username || ' ' ||
'QUOTA ' || v_new_quota || ' ' ||
'ON ' || v_object_name;
fixer.fix_this(v_sql);
END IF; -- Превышение квоты табличного пространства
END IF; -- Ошибка, связанная с пространством
END;
Этот фрагмент создает триггер, который срабатывает каждый раз, когда команда переходит в приостановленное состояние, и пытается решить проблему. (Обратите внимание: в этом конкретном примере исправляются только ошибки превышения квот табличного пространства.)
Теперь при возникновении проблем с квотами табличного пространства триггер AFTER SUSPEND уровня базы данных срабатывает и помещает команду SQL в таблицу при помощи служебного пакета fixer. Задание fixer, работающее в фоновом режиме, выбирает команду SQL из таблицы и выполняет ее, решая проблему с квотой без лишних телефонных звонков.
Полный код триггера AFTER SUSPEND и пакета fixer содержится в файле fixer.sql на сайте github.
Функция ORA_SPACE_ERROR_INFO
Информацию о причине приостановки команды можно получить при помощи функции ORA_SPACE_ERROR_lNFO, представленной в предшествующих примерах. Рассмотрим синтаксис вызова этой функции; ее параметры перечислены в табл. 1.
Таблица 1. Параметры ORA_SPACE_ERROR_INFO
| Строки | описание |
| тип_ошибки | Тип ошибки, связанной с пространственной ошибкой; одно из следующих значений: – SPACE QUOTA EXCEEDED: пользователь превысил свою квоту табличного пространства– MAX EXTENTS REACHED: объект пытается выйти за пределы заданного максимального количества сегментов– NO MORE SPACE: в табличном пространстве не хватает места для сохранения новой информации |
| тип_объекта | Тип объекта, для которого произошла пространственная |
| владелец_объекта | Владелец объекта, для которого произошла пространственная ошибка |
| имя_табличного_пространства | Табличное пространство, для которого произошла пространственная ошибка |
| имя_объекта | Имя объекта, для которого произошла пространственная |
| имя_подобъекта | Имя подобъекта, для которого произошла |
Функция возвращает логическое значение TRUE, если приостановка происходит из-за одной из ошибок, перечисленных в таблице, или FALSE в противном случае.
Функция ORA_SPACE_ERROR_INFO не исправляет проблемы с пространством в вашей системе; она всего лишь предоставляет информацию, необходимую для выполнения дальнейших действий. Вы уже видели, как решалась проблема с квотой в предыдущем примере. Ниже приводятся два дополнительных примера SQL, которые могут использоваться для решения проблем с пространством, выявляемых функцией ORA_SPACE_ERROR_INFO:
- Таблица (или индекс) достигла максимального количества сегментов, и дополнительные сегменты недоступны:
ALTER тип_объекта владелец_объекта. имя_объекта STORAGE (MAXEXTENTS UNLIMITED);- В табличном пространстве полностью закончилось место:
/* Предполагается использование Oracle Managed Files
(Oracle9i Database и выше), так что явное объявление файла данных
не обязательно */
ALTER TABLESPACE имя_табличного_пространства ADD DATAFILE;
Пакет DBMS_RESUMABLE
Если функция ORA_SPACE_ERROR_lNFO возвращает FALSE, значит, ситуация, вызвавшая приостановку команды, не может быть исправлена, а следовательно, разумных причин оставаться в приостановленном состоянии тоже нет. Такие команды можно прервать из триггера AFTER_SUSPEND при помощи процедуры ABORT из пакета DBMS_RESUMABLE. Пример:
TRIGGER after_suspend
AFTER SUSPEND
ON SCHEMA
DECLARE
CURSOR curs_get_sid IS
SELECT sid
FROM v$session
WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID');
v_sid NUMBER;
v_error_type VARCHAR2(30);
...
BEGIN
IF ORA_SPACE_ERROR_INFO(...
...Пытаемся исправить...
ELSE -- can't fix the situation
OPEN curs_get_sid;
FETCH curs_get_sid INTO v_sid;
CLOSE curs_get_sid;
DBMS_RESUMABLE.ABORT(v_sid);
END IF;
END;
Процедура ABORT получает один аргумент: идентификатор прерываемого сеанса. Это позволяет вызывать ABORT из триггеров AFTER SUSPEND из уровня DATABASE или SCHEMA. Прерванный сеанс получает ошибку ORA-01013 (пользователь запросил операцию отмены текущей операции).
Действительно, отмена была запрошена пользователем — но каким именно? Кроме процедуры ABORT, пакет DBMS_RESUMABLE содержит функции и процедуры для чтения и назначения тайм-аута:
- GET_SESSION_TIMEOUT — возвращает значение тайм-аута приостановленного сеанса по идентификатору сеанса:
FUNCTION DBMS_RESUMABLE.GET_SESSION_TIMEOUT (sessionid IN NUMBER)
RETURN NUMBER;
- SET_SESSION_TIMEOUT — задает значение тайм-аута приостановленного сеанса по идентификатору сеанса:
PROCEDURE DBMS_RESUMABLE.SET_SESSION_TIMEOUT (sessionid IN NUMBER,
TIMEOUT IN NUMBER);
- GET_TIMEOUT — возвращает значение тайм-аута текущего сеанса:
FUNCTION DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER;- SET_TIMEOUT — задает значение тайм-аута текущего сеанса:
PROCEDURE DBMS_REUSABLE.SET_TIMEOUT (TIMEOUT IN NUMBER);Новые значения тайм-аута вступают в действие немедленно, но не обнуляют счетчик.
Многократное срабатывание
Триггеры AFTER SUSPEND срабатывают при каждой приостановке команды. Следовательно, они могут многократно сработать во время выполнения одной команды. Например, представьте, что в системе реализован следующий жестко запрограммированный триггер:
TRIGGER after_suspend
AFTER SUSPEND ON SCHEMA
DECLARE
-- Получение нового максимума (текущий плюс 1)
CURSOR curs_get_extents IS
SELECT max_extents + 1
FROM user_tables
WHERE table_name = 'MONTHLY_SUMMARY';
v_new_max NUMBER;
BEGIN
- Выборка нового максимального количества сегментов
OPEN curs_get_extents;
FETCH curs_get_extents INTO v_new_max;
CLOSE curs_get_extents;
-- alter the table to take on the new value for maxextents
EXECUTE IMMEDIATE 'ALTER TABLE MONTHLY_SUMMARY ' ||
'STORAGE ( MAXEXTENTS ' ||
v_new_max || ')';
DBMS_OUTPUT.PUT_LINE('Incremented MAXEXTENTS to ' || v_new_max);
END;
Если начать с пустой таблицы со значением MAXEXTENTS (максимальное количество сегментов), равным 1, вставка данных объемом 4 сегмента дает следующий результат:
SQL> @test
Incremented MAXEXTENTS to 2
Incremented MAXEXTENTS to 3
Incremented MAXEXTENTS to 4
PL/SQL procedure successfully completed.
Исправлять или не исправлять?
Вот в чем вопрос! Предшествующие примеры показали, как ошибки нехватки места исправляются на ходу — команды приостанавливаются до того, как вмешательство (ручное или автоматизированное) позволит им продолжить работу. Если довести происходящее до крайности, этот метод позволяет устанавливать приложения с минимальными требованиями к табличному пространству, квотам и сегментам, а затем наращивать их по мере надобности. Слишком старательному администратору базы данных такая ситуация покажется идеальной, но у нее есть свои недостатки:
- Нерегулярные паузы — паузы с приостановкой команд могут породить хаос в крупномасштабных оперативных системах обработки транзакций (OLTP), требующих высокой пропускной способности. Ситуация дополнительно усугубляется, если на исправление уходит много времени.
- Конкуренция за ресурсы — приостановленные команды удерживают блокировку таблиц. Это может привести к простоям или сбоям при выполнении других команд.
- Непроизводительные затраты — ресурсы, необходимые для частого добавления сегментов или файлов данных или увеличения квот, могут оказаться неприемлемо высокими по сравнению с ресурсами, необходимыми для непосредственной работы приложения.
По этим причинам я рекомендую осторожно использовать триггеры AFTER SUSPEND. Они идеально подходят для долгих процессов, которые должны перезапускаться после сбоя, а также для пошаговых процессов, для перезапуска которых необходима отмена внесенных изменений командами DML. В приложениях OLTP они работают недостаточно хорошо.
