Как удалить временную таблицу oracle
О работе в Oracle Data Integrator (ODI) и других захватывающих вещах из мира BI.
- О блоге
- Поиск по ODI блогам
- Инсталляция
- Пожелания и ошибки
- Патчи
- Документация по ODI
- ODI 11g FAQ
- ODI Experts
- BI-Quotient
- John Goodwin
воскресенье, 17 октября 2010 г.
Удаление временных $ таблиц.
Сегодня небольшая заметка по мотивам вот этой записи, посвященная удалению временных таблиц из БД.
Задачи по обработке данных, при которых используются стандартные модули знаний (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);
WHILE (V_COUNT > 0) LOOP
stmt := 'DROP TABLE ' || '' || '.' || V_TABLE || ' CASCADE CONSTRAINTS PURGE';
EXECUTE IMMEDIATE stmt;
commit;
V_COUNT := V_COUNT - 1;
Данный код работает в том случае, если временные таблицы создаются в СУБД Oracle.
Добавьте к процедуре опцию, через которую будет необходимо передавать название схемы БД, в которой модули знаний ODI создают временные таблицы.
На рисунке показано, что для этой опции задано значение по умолчанию, таким образом, эта процедура может быть вызвана напрямую, без пакета.
Процедура удаляет временные таблицы, созданные больше месяца назад, при этом имя таблицы может начинаться с символов C$_ или I$_ или E$_.
Модифицируйте этот перечень, если ваши префиксы временных таблиц выглядят по-другому. Список префиксов можно узнать в Менеджере Топологий, на закладке физической архитектуры, в свойствах сервера данных. Выглядит это примерно так:
Кстати, там же указывается рабочая и временные схемы БД для этого сервера данных:
- Главная /
- Статьи /
- Oracle /
- RMAN В ПРИМЕРАХ - Конфигурирование окружения RMAN. Глава 4. Часть 1.
Временные таблицы Oracle
В ременные таблицы впервые появились в Oracle8I. Они предназначались для хранения данных на протяжении сеанса или транзакции. Отличительной особенностью этих таблиц являлось то, что они располагались во временных сегментах и данные в этих таблицах хранились только на период сессии или транзакции в зависимости от реализации. Поэтому они нашли большое применение в качестве промежуточных таблиц при расчётах, отчетах и оптимизации сложных запросов.
Для создания временных таблиц используется оператор CREATE GLOBAL TEMPORARY TABLE с ключевыми словами ON COMMIT PRESERVE ROWS (хранение данных на время сеанса) или ON COMMIT DELETE ROWS (хранение данных на время транзакции).
В качестве примера попробуем создать временную таблицу на сеанс и заполнить её данными:
Видно, что после отключения сеанса, данные из таблицы удаляются. Теперь создадим и заполним временную таблицу на время транзакции:
Данные из таблицы удалились сразу после завершения транзакции. Отличительной особенностью временной таблицы в данном случае является то, что данные таблицы не только удаляются, но и невидны из других сеансов. То есть пользователи могут одновременно использовать одну и туже временную таблицу, не пересекаясь данными. Отсюда вытекают некоторые ограничения при работе с временными таблицами. Так нельзя блокировать таблицу с помощью команды LOCK TABLE. Команда выполниться без ошибок, но блокировка не установиться:
Нельзя добавлять внешние ключи на временную таблицу и наоборот:
Не поддерживается так же перенос временной таблицы в другое табличное пространство:
Временные таблицы при DML операциях не генерируют информации повторного выполнения, так как изначально создаются в режиме NOLOGGING (смотри Использование режима NOLOGGING. Часть II). Но при этом они поддерживают механизм отката изменений, как и для обыкновенной таблицы:
Кстати об этом забывают, и отсюда возникает предубеждение, что commit после заполнения временной таблицы (для таблицы на сеанс) можно не ставить. Это обычно приводит к проблемам функционирования сегментов отката. Данные, помещённые в сегмент отката после заполнения временной таблицы и не фиксации изменений транзакции, будут находиться там до отключения сеанса, препятствуя схлопыванию сегментов отката. Отсюда вывод: не забывайте ставить commit для временных таблиц.
Для ускорения работы с временными таблицами можно создавать индексы. Они также располагаются во временном табличном пространстве и заполняются при вставке данных в таблицу. Но если вы попытаетесь создать индекс, после того как какой либо сеанс уже вставил в неё данные, то вам выдастся ошибка:
Это утверждение верно также для всех DDL команд применяемых к временной таблице. Теперь рассмотрим оптимизацию SQL при работе с временными таблицами. Одним из заблуждений при работе с временными таблицами является то, что можно проводить их анализ с целью собрать статистику для оптимизатора. Временная таблица по своему определению не может иметь постоянную статистику, следовательно, оптимизатор при построении плана запроса строит план выполнения исходя не из статистики, а из предположения по умолчанию. К данному заблуждению может подтолкнуть безошибочное выполнение команды ANALYZE. Но, просмотрев представление DBA_TABLES после выполнения данной команды, мы не обнаружим статистики для данной таблицы. Более честно поступает в этом случае пакет dbms_stats, прямо сообщая нам, что не поддерживает сбор статистики для временных таблиц:
Рассмотрим всё это на примере. Для этого создадим индекс на таблицу table1, заполним данными, проанализируем её и выберем почти все записи, предварительно включив вывод плана выполнения:
Как видно, оптимизатор не выдал никакой оценки стоимости и использовал предположения по умолчанию. Иногда этого недостаточно. Но выход в этом случае есть. На самом деле статистику для временной таблицы можно установить принудительно, используя для этого пакет dbms_stats. Попробуем сделать это, получив статистику с аналогичной не временной таблицы table3:
Как показывают планы выполнения двух последних запросов, статистика для таблицы table1 всё же была установлена, и при этом оптимизатор её использовал (правило 5 %). Последнее что нам осталось это рассмотреть, как выделяется и освобождается табличное пространство для временных таблиц.
Исходя из своего определения, временной таблице нельзя заранее выделить необходимое пространство во временном сегменте. Это делает сам Oracle при команде INSERT. Рассмотрим, как это происходит на примере. Для начала посмотрим, сколько блоков всего и свободно в табличном пространстве TTEMP, где расположена временная таблица table1:
Табличное пространство почти свободно. Теперь заполним таблицу table1:
Экстенты для таблицы выделяются при выполнении операции вставки. Посмотрим, сколько осталось свободных блоков в табличном пространстве:
Таблица заняла в TTEMP 20 блоков. Это 2 экстента. Проверим:
Для получения более точной информации по использованию табличного пространства сеансами нужно сделать запрос к следующему представлению:
Итак, таблица заняла 2 экстента по 10 блоков каждый, при этом один экстент выделен под данные, другой под индексы. Если мы теперь сделаем DISCONNECT, то увидим, что выделенные экстенты под временную таблицу table1 освободились.
Но в тоже время мы видим, что число выделенных экстентов в табличном пространстве не уменьшилось:
Отсюда может сложиться заблуждение, что экстенты не освобождены. На самом деле, как я предполагаю, информация в словаре (а представления dba_free_space и dba_extents построены именно на нём) меняется для временных табличных пространств только при первом выделении экстентов. Это вполне может, связано с большими накладными расходами по обновлению словаря. Представление v$sort_usage наоборот построено на x$ таблице и поэтому информация в нём более верна.
GLOBAL TEMPORARY
Что же такое временные таблицы, и зачем они нужны?
Давайте разберемся.
Временные таблицы используются в Oracle для хранения данных, которые относятся к одной сессии или одной транзакции.
Причем применение Oracle временных таблиц существенно отличается от применения временных таблиц в том же MS SQL. ORACLE временная таблица это тот же DDL обьект со всеми ограничениями , в MS SQL подобных ограничений нет.
итак подробнее
Так, например, целесообразно использовать временные таблицы для хранения данных об открытых неким клиентским приложением или процессом файлах, об открытых дочерних формах. Можно так же сохранять во временных таблицах, данные матриц преобразований в сложных математических задачах.
в общем, временные таблицы( GLOBAL TEMPORARY ), а отличие от таблиц регулярных целесообразно использовать в тех случаях , когда сохраняемые данные часто изменяются, и непостоянны.
Временные таблицы ( GLOBAL TEMPORARY TABLE ) отличаются от обычных регулярных таблиц тем, что эти таблицы предназначены только для хранения временных для некоторой сессии данных.
Данные во временной таблицы будут видны только в той сессии, которая вставила эти данные в таблицу.
После создания временной таблицы ее описание сохраняется в словаре данных ORACLE, но в этот момент не определяется сегмент , где будут сохранятся данные из этой таблицы.
Место в соответствующем сегменте под данные, выделяется динамически, в момент обращения первой команды манипулирования данными - DML (select, insert, update) к этой временной таблице.
Временная таблица описывается таким же образом, что и обычная регулярная таблица, но сегменты таблицы, и все данные такой таблицы, подразделяются :
- данные используемые только в данной сессии
- или же данные используемые только в данной транзакции
Специфику поведения данных относительно сессии определяет ключевые слова ON COMMIT и ON PRESERVE в команде CREATE TABLE
Можно использовать операторы определения данных DDL такие как (
) для временных таблиц, но только тогда когда сессия не обращается к временной таблице, не связана с ней. Сессия связана с временной таблицей и при выполнении команды INSERT над данными таблицы.
Существует несколько способов, чтобы сделать сессию несвязанной с временной таблицей:
1. Использовать команду truncate для данной таблицы.
2. Аннулировать данную сессию
3. Использовать команды фиксации работы транзакции COMMIT или ROLLBACK
Ограничения для временных таблиц в ORACLE
На временные таблицы в ORACLE распространяются следующие ограничения :
1. Временная таблица в Oracle не может быть партицирована particioned, кластеризована clustered, или быть организованной по индексу index organized.
2. Нельзя связывать вторичные ключи с колонками временной таблицы
3. Временная таблица не может включать в себя вложенных таблиц (nested table)
4. Нельзя использовать в описании временных таблиц следующие команды :LOB_storage_clause: TABLESPACE, storage_clause, или logging_clause
5. Подсказка Parallel и параллельные запросы не поддерживаются во временных таблицах
6. Распределенные транзакции так же не могут работать с временными таблицами
7. Сегментирование не работает в временных таблицах
Создание временной таблицы
Синтаксис для создания временных таблиц практически аналогичен синтаксису для создания регулярных таблиц, но есть некоторые операторы, которые используются только для временных таблиц:
ОN COMMIT DELETE ROWS используется во временных таблицах, данные которой существуют в пределах одной транзакции.
Oracle удаляет все строки - все данные из временной таблицы после завершения транзакции, после выполнения команды COMMIT .
Данная инструкция подразумевается по умолчанию, то есть при создании временной таблицы - она всегда будет ОN COMMIT DELETE ROWS
ОN COMMIT PRESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. СУБД Oracle удаляет все строки из временной таблицы - очищает таблицу после завершения сессии.
Примеры
Данные примеры продемонстрируют создание и использование временных таблиц, отличия временных таблиц ОN COMMIT DELETE ROWS и ОN COMMIT PRESERVE ROWS , отличие временных таблиц от таблиц регулярных.
Все примеры построены на основе стандартной демонстрационной схемы SCOTT БД ORACLE
Пример создания временной таблицы:
Данный пример демонстрирует создание временной таблицы, данные которой, относятся к сессии.
Пример создания временной таблицы с использованием запроса
Данный пример демонстрирует создание временной таблицы на основе запроса, заполненные данные для этой таблицы актуальны только для текущей сессии. Если попробовать прочитать таблицу min_salesemp из другой сессий, то таблица будет пустой.
Пример создания временной таблицы с объектным типом колонки
Этот пример демонстрирует, что использование объектных типов данных, вполне правомерно для временных таблиц.
Использование индексов во временных таблицах
Данный пример показывает, что во временных таблицах можно использовать индексы и ограничения - CONSTRAINT , как и в регулярных таблицах.
Отличие временной таблицы от таблицы регулярной
Демонстрация отличия временной таблицы, от таблицы регулярной.
Соединяемся с БД, создаем две таблицы
временную
-- Не будем нагружать скрипты излишним синтаксисом
Добавляем данные
Выполняем запрос к временной таблице
Результат
---
1
2
3
Закрываем сессию, соединяемся с БД снова. Выполняем запрос к временной таблице
К регулярной таблице
Результат
---
1
2
3
Итак, данные во временной таблице сохраняются только в текущей сессии или транзакции в зависимости от выражения ON COMMIT в скрипте создания таблицы.
Отличие временной таблицы ОN COMMIT PRESERVE ROWS от временной таблицы ОN COMMIT DELETE ROWS
Создаем две таблицы ON COMMIT PRESERVE ROWS
Добавляем данные в созданные таблицы
Результат запроса
--
1
2
3
Выполняем команду COMMIT после чего вновь выполняем запросы
Итак , данные в таблице ОN COMMIT DELETE ROWS не сохраняются после выполнения команды COMMIT, в отличие от временной таблицы ON COMMIT PRESERVE ROWS
Итог : после прочтения данного материала становиться понятно следующее:
что такое временные таблицы в ORACLE
назначение временных таблиц
область применения временных таблиц в ORACLE
ограничения для временных таблиц
отличие временных таблиц от регулярных таблиц
отличие временных таблиц с данными уровня сессии от временных таблиц с данными уровня транзакции (ON COMMIT PRESERVE ROWS ,ОN COMMIT DELETE ROWS).
Создание временной таблицы
Синтаксис для создания временных таблиц практически аналогичен синтаксису для создания регулярных таблиц, но есть некоторые операторы, которые используются только для временных таблиц:
О N COMMIT DELETE ROWS используется во временных таблицах, данные которой существуют в пределах одной транзакции.
Oracle удаляет все строки - все данные из временной таблицы после завершения транзакции, после выполнения команды COMMIT .
Данная инструкция подразумевается по умолчанию, то есть при создании временной таблицы - она всегда будет О N COMMIT DELETE ROWS
О N COMMIT PRESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. СУБД Oracle удаляет все строки из временной таблицы - очищает таблицу после завершения сессии.
Данные примеры продемонстрируют создание и использование временных таблиц, отличия временных таблиц О N COMMIT DELETE ROWS и О N COMMIT PRESERVE ROWS , отличие временных таблиц от таблиц регулярных.
Все примеры построены на основе стандартной демонстрационной схемы SCOTT БД ORACLE
• Пример создании временной таблицы:
CREATE GLOBAL TEMPORARY TABLE scott.temp_dept
deptno NUMBER ( 2 , 0 ),
dname VARCHAR2 ( 14 )
) ON COMMIT PRESERVE ROWS ;
Данный пример демонстрирует создание временной таблицы, данные которой, относятся к сессии.
• Пример создания временной таблицы с использованием подзапроса
CREATE GLOBAL TEMPORARY
TABLE scott.min_salesemp
ON COMMIT PRESERVE ROWS
AS SELECT * FROM scott.emp WHERE sal < 2000 ;
Данный пример демонстрирует создание временной таблицы на основе подзапроса, заполненные данные для этой таблицы актуальны только для текущей сессии. Если попробовать прочитать таблицу min _ salesemp из другой сессий, то таблица будет пустой.
• Пример создания временной таблицы с объектным типом колонки
CREATE TYPE scott.person_t AS OBJECT ( NAME VARCHAR2 ( 100 ), ssn NUMBER );
CREATE GLOBAL TEMPORARY TABLE scott.tmp_obj
ON COMMIT DELETE ROWS ;
Этот пример демонстрирует, что использование объектных типов данных, вполне правомерно для временных таблиц.
• Использование индексов во временных таблицах
CREATE GLOBAL TEMPORARY TABLE scott.city_dept
deptno NUMBER ( 2 , 0 ),
dname VARCHAR2 ( 14 ),
CONSTRAINT pk_city_dept PRIMARY KEY (deptno)
ON COMMIT DELETE ROWS ;
COMMENT ON COLUMN scott.city_dept.deptno IS 'DEPARTMENT NUMBER' ;
COMMENT ON COLUMN SCOTT.CITY_DEPT.DNAME IS 'DEPARTMENT NAME' ;
CREATE UNIQUE INDEX scott.pk_city_dept_dname ON scott.city_dept (deptno,dname);
CREATE INDEX scott.idx_dname ON scott.city_dept (dname);
Данный пример показывает, что во временных таблицах можно использовать индексы и ограничения - CONSTRAINT , как и в регулярных таблицах.
Читайте также: