Удаление временных $ таблиц.

Сегодня небольшая заметка по мотивам вот этой записи, посвященная удалению временных таблиц из БД.

Задачи по обработке данных, при которых используются стандартные модули знаний (knowledge modules) ODI предполагают создание временных таблиц и их использование в процессе ETL преобразований. Например, модуль загрузки данных из файла в таблицу, создаст в БД временную таблицу С$_MY_FILE_DATA, загрузит "сырые" данные туда, и уже после этого перенесет данные в таблицу MY_FILE_DATA.

Похожий процесс использования временных таблиц происходит не только в модулях загрузки, но и в модулях интеграции (Integration Knowledge Module, IKM) и в модулях журнализации и т.п.

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

Тем не менее, с течением времени, таких ненужных таблиц накапливается все больше. Чтобы их автоматически удалять, разово или по расписанию, можно сделать описанное ниже.

Создаем процедуру ODI и один шаг в ней. Выбираем технологию Oracle и соответствующую схему. Добавляем в текст шага процедуры следующий код:

DECLARE
V_COUNT NUMBER;
V_TABLE VARCHAR2(300);
stmt VARCHAR2(300);

BEGIN
SELECT COUNT(DISTINCT object_name) INTO V_COUNT FROM all_objects WHERE
owner = '<%=odiRef.getOption("TEMP_SCHEMA")%>'
AND (object_name LIKE 'I$_%' OR object_name LIKE 'C$_%' OR object_name LIKE 'E$_%')
AND object_type = 'TABLE'
AND sysdate - NVL(last_ddl_time, created) > 31;

WHILE (V_COUNT > 0) LOOP

SELECT DISTINCT object_name INTO V_TABLE FROM all_objects WHERE
owner = '<%=odiRef.getOption("TEMP_SCHEMA")%>'
AND (object_name LIKE 'I$_%' OR object_name LIKE 'C$_%' OR object_name LIKE 'E$_%')
AND object_type = 'TABLE'
AND sysdate - NVL(last_ddl_time, created) > 31
AND rownum = 1;

stmt := 'DROP TABLE ' || '' || '.' || V_TABLE || ' CASCADE CONSTRAINTS PURGE';

EXECUTE IMMEDIATE stmt;
commit;

V_COUNT := V_COUNT - 1;

END LOOP;
END;

Данный код работает в том случае, если временные таблицы создаются в СУБД Oracle.

Добавьте к процедуре опцию, через которую будет необходимо передавать название схемы БД, в которой модули знаний ODI создают временные таблицы.

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

Процедура удаляет временные таблицы, созданные больше месяца назад, при этом имя таблицы может начинаться с символов C$_ или I$_ или E$_.
Модифицируйте этот перечень, если ваши префиксы временных таблиц выглядят по-другому. Список префиксов можно узнать в Менеджере Топологий, на закладке физической архитектуры, в свойствах сервера данных. Выглядит это примерно так:

Кстати, там же указывается рабочая и временные схемы БД для этого сервера данных: