Открыто о СУБД Oracle на русском : динамическое пересоздание глобальных временных таблиц

Выпуск 81

Уважаемые подписчики рассылки!

Еще 6 января, в предыдущем выпуске рассылки, я объявил о прекращении переводов публикаций Тома Кайта и других общепризнанных экспертов, а также обратился к вам с просьбой прислать свои пожелания и рекомендации по дальнейшему развитию рассылки в 2005 году.

Я получил несколько отзывов (6, при более чем 9000 подписчиков!). Меня просили продолжить переводить и комментировать обсуждения с сайта asktom.oracle.com, обсуждать проблемы, с которыми я сталкивался, и методы их решения, использование больших баз данных Oracle и применение Oracle в небольших проектах. Вас интересуют "кирпичики" знаний, в том числе, широко известные, но важные вещи, вроде работы оптимизатора, использования индексов и подсказок, стратегии применения битовых индексов и материализованных представлений.

А еще мне порекомендовали предложить вам, уважаемые подписчики, публиковать в рассылке ваши статьи. Открыть такую новую рубрику. (Собственно, один раз так уже было - Сергей Творошенко обратился ко мне с предложением опубликовать статью об использовании хранимых шаблонов, и я сделал это, причем, раньше, чем редакция журнала Oracle Magazine RE...) С автором рекомендации (Alex Volny) завязалась достаточно интересная дискуссия, в результате которой в этом выпуске и публикуется его статья, с моими минимальными редакционными правками и примечаниями.

Небольшое количество отзывов, а также привычный для этого времени года "разброд в мыслях" привели к тому, что вместо выпусков рассылки я занялся организацией "живых" семинаров, посвященных Oracle. Я провел их уже 6, по 1,5 часа, уделив основное внимание особенностям работы оптимизатора, а также использованию подсказок. Один из семинаров был посвящен правильному подходу к разработке приложений для Oracle (небольшой иллюстрированный пересказ содержания первой главы книги Тома Кайта "Effective Oracle by Design", перевести которую мне шанс пока так и не представился). Я собирался сразу же публиковать материалы этих семинаров в рассылке, но оказалось, что презентация для семинара сильно отличается по стилю и оформлению от типичного выпуска рассылки, поэтому соответствующие выпуски все еще не готовы.

Заодно выяснилось, что семинары эти вызывают внимания немногим больше, чем обращения и вопросы, которые я задавал в рассылке. Тем не менее, осознавая всю безнадежность привлечь чье-то внимание, заявляю:

Я готов проводить полуторачасовые семинары по Oracle в Киеве в любой аудитории в любой день недели (это пока, до конца марта - потом могут появиться ограничения). Если мне покажется интересной тема, я с удовольствием выступлю в Киеве бесплатно. Если будет что сказать и показать, конечно... Предложения от жителей областных центров Украины и ближайшего зарубежья тоже принимаются. Вобщем, жду ваших писем на эту тему.

А теперь, собственно, статья...

Динамическое пересоздание глобальных временных таблиц

Alex Volny

Введение

Иногда приходиться решать задачи управления таблицами с отличной от классических канонов структурой. По ряду причин, такие таблицы растут как вниз (добавление строк данных), так и вправо (добавление столбцов). Причиной может быть исторически принятая, унаследованная структура данных (например, приложение, портированное в СУБД Oracle без перепроектирования структур), или проблемы производительности на этапе сложного расчета.

Например, базовые данные находятся в правильном, нормализованном представлении, а на начальном этапе многоступенчатого расчета производиться "динамическое распрямление вправо" по слабо изменяемому признаку и заполнение заранее созданных глобальных временных таблиц (Global Temporary Table - GTT). Результаты расчета могут быть затем агрегированы или "схлопнуты" по какому-либо признаку.

Это может быть актуально, когда объем данных в одном сеансе расчета очень значителен (сотни тысяч или миллионы строк). Как правило, это расчетные задачи OLAP (DSS), например, расчет времени и скорости продаж товаров по всей сети магазинов, прогноз товарного запаса, расчет материального баланса. В результате такого представления GTT, "высота" таблицы сокращается пропорционально количеству магазинов сети (слабо изменяемому признаку), например в 50 раз, с 30 млн. до 600 тыс. строк для каждого типа данных (остатки, продажи и т.д.).

Я не стану давать оценок таким структурам с точки зрения классического проектирования, скажу лишь, что они используются и, на этапе расчета, могут давать значительный выигрыш в производительности. Тем более, если динамика роста вправо приемлема (ограничение в Oracle 9i - 1000 столбцов). Реализация такого расчетного механизма возможна, например, с применением динамического SQL и наборов (collections).

Итак, каждый сеанс использует для расчетов некоторый набор PL/SQL-пакетов, хранит данные своего расчета в наборе временных таблиц (например, уровня сеанса, ON COMMIT PRESERVE ROWS).

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

Алгоритм действий

1. Получение списка блокирующих сеансов. Тип блокировок - 'TO'¹ для GTT.
2. Оповещение с помощью DBMS_ALERT всех "думающих" сеансов и повторный запрос списка блокирующих сеансов.
3. Принудительное завершение работы ("убивание") блокирующих сеансов.
4. Проверка и "убивание" зависших KILLED-сеансов для текущего ORACLE_SID. При этом используется технология выполнения комманд ОС из PL/SQL: надо "убить" процесс или нить зависшего сеанса (варианты для UNIX- и для Windows-платформы, притом непосредственно из PL/SQL, без использования внешних заданий и планировщиков).
5. Пересоздание всех нужных временных таблиц и индексов.
6. Перекомпиляция всех или выбранных INVALID-объектов.

1. Кстати, что-то я не обнаружил в документации описания этого типа блокировки. А ведь, как легко убедиться, именно такие блокировки устанавливаются, когда сеанс вставил какие-то данные в глобальную временную таблицу уровня сеанса... - Примечание В.К.

Полный текст пакета представлен в Приложении 2, я же остановлюсь на необходимых деталях и дополнениях.

Тонкости реализации

1. Получение списка блокирующих сеансов и блокировки GTT

При первой вставке в глобальную временную таблицу сервер Oracle устанавливает на нее блокировку 'TO', которая удерживается при любых дальнейших изменениях, до наступления одного из двух условий:

• завершение сеанса или фиксация транзакции (в зависимости от типа GTT, указанного при создании: ON COMMIT PRESERVE ROWS или ON COMMIT DELETE ROWS);
• выполнение оператора TRUNCATE TABLE.

Таким образом, чтобы найти блокирующий сеанс, необходимо искать блокировки любой пересоздаваемой глобальной временной таблицы, участвующей в расчетах (в нашем примере - ZZZ_TEST).

Установки статуса INVALID для создаваемого пакета TM_UTIL (и дальнейшую перекомпиляцию самого себя) можно избежать, используя динамический SQL в теле пакета.

В результате, имеем два фильтра для поиска блокирующего сеанса (тип блокировки и имя GTT). В качестве параметров дальнейшей команды ALTER SYSTEM KILL SESSION подаем полученные идентификаторы сеанса, SID и SERIAL#.

 
      -- Курсор наличия блокирующих сеансов 
      CURSOR SESS_bl_cur 
      IS 
        SELECT distinct VS.SID, VS.SERIAL# 
        FROM V$SESSION VS, V$LOCK VL, DBA_OBJECTS OBJ 
        WHERE OBJ.OBJECT_NAME = 'ZZZ_TEST' AND 
        VL.TYPE='TO' AND 
        VL.ID1=OBJ.OBJECT_ID AND 
        VL.SID=VS.SID; 
 
      -- Запись для выборки из курсора  
      sess_bl_rec  SESS_bl_cur%ROWTYPE; 

Чтобы пакет смог успешно обратиться к соответствующим представлениям словаря данных, необходимо выдать пользователю-создателю пакета следующие привилегии:

 
   GRANT SELECT ON  SYS."V_$SESSION" TO "SHOPS_MOD" 
   GRANT SELECT ON  SYS."V_$LOCK" TO "SHOPS_MOD" 
   GRANT SELECT ON  SYS."DBA_OBJECTS" TO "SHOPS_MOD" 

Потребуются также (см. далее) привилегии:

 
   GRANT SELECT ON  SYS."V_$PROCESS" TO "SHOPS_MOD" 
   GRANT SELECT ON  SYS."V_$INSTANCE" TO "SHOPS_MOD" 

Впрочем, если безопасность и принцип минимальности привилегий вас не беспокоят, достаточно будет пары операторов:

 
   GRANT SELECT ANY TABLE TO "SHOPS_MOD"; 
   GRANT SELECT ANY DICTIONARY TO "SHOPS_MOD"; 

2. Оповещение с помощью DBMS_ALERT "думающих" сеансов

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

 
    PROCEDURE Alert_msg 
    (a_alert_name in VARCHAR2, a_alert_msg IN VARCHAR2) 
    AS 
    PRAGMA AUTONOMOUS_TRANSACTION; 
    Begin 
      -- Пошлем сообщение всем, кто зарегистрировался для получения нашего сигнала 
      DBMS_ALERT.signal(a_alert_name, a_alert_msg);          
      COMMIT; 
    end Alert_msg; 

3. Прекращение работы блокирующих сеансов

Реализуем стандартным методом:

 
   ALTER SYSTEM KILL SESSION 'int1, int2' 
   

где параметры - полученные ранее SID и SERIAL#.

Более мягкий вариант:

 
   ALTER SYSTEM DISCONNECT SESSION 'int1 , int2' POST_TRANSACTION  IMMEDIATE 

4. Проверка и "убивание" зависших KILLED-сеансов для текущего ORACLE_SID

Часто происходит так, что после завершения сеанса командой ALTER SYSTEM, сервер Oraсle не завершает сеанс, а присваивает ему статус 'KILLED'. Такие "зависшие" сеансы не освобождают ресурсы сервера (блокировки, защелки и т.д.), что не позволяет, в частности, пересоздавать использовавшиеся ими временные таблицы.

Справедливости ради стоит заменить, что в версии 9i ситуация кардинально улучшилась.

Для решения проблемы нужно "убить" серверный процесс (нить) зависшего KILLED-сеанса. В зависимости от платформы, существуют следующие варианты:

1.(NT): Использую Oracle-утилиту orakill. Добавляю instance_name, полученный из v$instance:

 
         SELECT 'orakill ' || i.instance_name || ' ' || p.spid as Kill_cmd  
         FROM v$process p, v$session s, v$instance i 
         WHERE p.addr = s.paddr AND  
         s.status = 'KILLED'; 

2.(SUSE Linux): Использую стандартную утилиту kill:

 
         SELECT '/bin/kill -s KILL ' || p.spid as Kill_cmd 
         FROM v$process p, v$session s 
         WHERE p.addr = s.paddr AND 
         s.status = 'KILLED'; 

(В представленном далее коде пакета я рассматриваю вариант реализации на SUSE Linux).

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

Фактически, речь идет о технологии выполнения команд ОС из PL/SQL. Это можно сделать с применением Java в Oracle. Создается java-класс OSUtil и функция-обертка, RUN_CMD. Плюс, для удобства представления результатов через DBMS_OUTPUT, можно создать процедуру RC.

 
------------------------------------------------------------------ 
-- 4.1. Технология выполнения команд ОС из PL/SQL 
------------------------------------------------------------------- 
create or replace and compile java source named "OSUtil" as 
  import java.io.*; 
  import java.lang.*; 
  public class OSUtil extends Object 
  { 
    public static int RunThis(String args) 
    { 
      Runtime rt = Runtime.getRuntime(); 
      int     rc = -1; 
      try 
      { 
        Process p = rt.exec(args); 
        int bufSize = 4096; 
        BufferedInputStream bis = 
          new BufferedInputStream(p.getInputStream(), bufSize); 
        int len; 
        byte buffer[] = new byte[bufSize]; 
 
        // Echo back what the program spit out 
        while ((len = bis.read(buffer, 0, bufSize)) != -1) 
          System.out.write(buffer, 0, len); 
        rc = p.waitFor(); 
      } 
      catch (Exception e) 
      { 
        e.printStackTrace(); 
        rc = -1; 
      } 
      finally 
      { 
        return rc; 
      } 
    } 
  } 
/ 
-------------------------------------------------------------- 
create or replace function RUN_CMD( p_cmd  in varchar2) return number 
  AUTHID CURRENT_USER as language java 
  name 'OSUtil.RunThis(java.lang.String) return integer'; 
/ 
-------------------------------------------------------------- 
create or replace procedure RC(p_cmd in varchar2) 
as 
x number; 
begin 
  x := run_cmd(p_cmd); 
  DBMS_OUTPUT.PUT_LINE('run_cmd returned : '||rpad(x, 3, ' ')||' for '||p_cmd); 
end; 
/ 
 
------------------------------------------------------------------------------------- 
-- 4.2. Дать привилегии (от имени SYS), на выполнение команды (утилиты) 
------------------------------------------------------------------------------------- 
begin 
  dbms_java.grant_permission ( 
    'SHOPS_MOD', 
    'SYS:java.io.FilePermission', 
    '/bin/kill', 
    'execute' 
  ); 
     
  dbms_java.grant_permission ( 
    'SHOPS_MOD', 
    'SYS:java.lang.RuntimePermission', 
    '*', 
    'writeFileDescriptor' 
  ); 
end; 
/ 
 
------------------------------------------------------------------------------------- 
-- 4.3.  Сымитируем пакетное выполнение: 
------------------------------------------------------------------------------------- 
-- В первом сеансе, можно проверить свой SID: 
select SID, SERIAL# from v$session where audsid=userenv('SESSIONID'); 
 
-- Из другого сеанса можно завершить первый (используя SID как параметр команды kill) 
set serveroutput on size 1000000; 
exec dbms_java.set_output(1000000); 
 
exec shops_mod.rc('/bin/kill -s KILL 11630'); 

5. Пересоздание всех необходимых временных таблиц и индексов

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

6. Перекомпиляция всех или выбранных INVALID-объектов

Понятно, что после пересоздания временных таблиц все зависимые от них объекты станут недействительными (INVALID), поэтому наша задача их перекомпилировать.

Необходимо предотвратить перекомпиляцию пакетом самого себя (TM_UTIL). Это можно сделать двумя способами:

• добавить явный фильтр на имя пакета 'TM_UTIL' в курсор RECOMPILE_cur.
• избежать установки статуса INVALID для пакета 'TM_UTIL', используя динамический SQL в теле пакета.

Я буду использовать последний вариант и перекомпилировать объекты только одной схемы, SHOPS_MOD.

Если понадобится перекомпилировать зависимые объекты во всех схемах (уже от имени SYSTEM), нужно сделать UNION c 'alter session set current_schema owner'. Таким образом, запрос меняется:

 
SELECT DISTINCT 'alter session set current_schema=' || 
                owner || 
                ';' cmd, 
                owner, 
                1 order_col, 
                NULL object_name 
  FROM dba_objects 
 WHERE status = 'INVALID' 
   AND object_type IN ('PACKAGE', 
                       'PACKAGE BODY', 
                       'VIEW', 
                       'PROCEDURE', 
                       'FUNCTION', 
                       'TRIGGER') 
UNION 
SELECT 'ALTER ' || 
       DECODE ( 
          object_type, 
          'PACKAGE BODY', 'PACKAGE', 
          object_type 
       ) || 
       ' ' || 
       owner || 
       '.' || 
       object_name || 
       ' COMPILE' || 
       DECODE ( 
          object_type, 
          'PACKAGE BODY', ' BODY', 
          '' 
       ) || 
       ';' cmd, 
       owner, 
       2 order_col, 
       object_name 
  FROM dba_objects outer 
 WHERE status = 'INVALID' 
   AND object_type IN ('PACKAGE', 
                       'PACKAGE BODY', 
                       'VIEW', 
                       'PROCEDURE', 
                       'FUNCTION', 
                       'TRIGGER') 
   AND  ( object_type <> 
             'PACKAGE BODY' 
       OR NOT EXISTS ( SELECT NULL 
                         FROM dba_objects 
                        WHERE owner = 
                                 outer.owner 
                          AND object_name = 
                                 outer.object_name 
                          AND object_type = 
                                 'PACKAGE' 
                          AND status = 
                                 'INVALID') 
          ) 
 ORDER BY 2, 3, 4 

Приложение 1: Тестовые таблицы

 
-- "Распрямленная" глобальная временная таблица для этапа расчета 
CREATE GLOBAL TEMPORARY TABLE ZZZ_TEST 
    (DATE_                         DATE, 
     ART                           VARCHAR2(30), 
    "ВВЦ_п"                        NUMBER, 
    "Л21_п"                        NUMBER, 
    "Мин_п"                        NUMBER, 
    "Икеа_п"                       NUMBER 
    -- ... 
    -- Магазинов может быть очень много 
) 
ON COMMIT PRESERVE ROWS; 
 
CREATE INDEX I_ZZZ_TEST ON ZZZ_TEST (ART ASC); 
 
-- Справочник объектов хранения (магазинов) 
CREATE TABLE N_OBJ_STORAGE 
    (ID_OBJ                        NUMBER(7,0) NOT NULL, 
    ID_PROJECT                     NUMBER(7,0), 
    ID_LOC                         NUMBER(7,0), 
    TYPE_                          VARCHAR2(20), 
    NAME_                          VARCHAR2(200), 
    SHORTNAME                      VARCHAR2(20), 
    ACCOUNTTD                      VARCHAR2(20), 
    ADDRESS                        VARCHAR2(200), 
    TELEPHONE                      VARCHAR2(100), 
    DATEOPEN                       DATE, 
    DATECLOSE                      DATE, 
    INUSE                          NUMBER(1,0), 
    STATUS                         VARCHAR2(1)    -- Флаг изменения записи [I,U,D] 
); 

Приложение 2: Исходный текст пакета TM_UTIL

 
------------------------------------------------------- 
PACKAGE TM_UTIL 
IS 
    TYPE TStringTab IS TABLE OF VARCHAR2(255) 
     INDEX BY BINARY_INTEGER; 
 
    PROCEDURE Alert_msg 
   (a_alert_name IN VARCHAR2, 
    a_alert_msg IN VARCHAR2); 
     
    FUNCTION KP_Recreate_TEST 
      RETURN NUMBER; 
    -- Возврат: 0 - Ошибка; 1 - Было пересоздание; 2 - Не было пересоздания 
END; 
 
------------------------------------------------------- 
PACKAGE BODY TM_UTIL 
IS 
    -- Вызов: 
   -- Alert_msg(alert_name, alert_msg); 
    PROCEDURE Alert_msg 
    (a_alert_name in VARCHAR2, a_alert_msg IN VARCHAR2) 
    AS 
    PRAGMA AUTONOMOUS_TRANSACTION; 
    Begin 
      -- Пошлем сообщение всем, кто зарегистрировался... 
      DBMS_ALERT.signal(a_alert_name,a_alert_msg);          
      COMMIT; 
    end Alert_msg; 
 
 
/*==========================================================================*/ 
/* Ввод/модификация нового Канала Реализации (К.Р.): 
/* Возврат: 0 - Ошибка; 1 - Было пересоздание; 2 - Не было пересоздания 
/*==========================================================================*/ 
    FUNCTION KP_Recreate_TEST 
        RETURN NUMBER 
    AS 
      -- Курсор факта изменения Обьекта хранения (поле STATUS: [I,U,D]). 
      -- Т.е. при удалении К.Р. запись не удаляется, а STATUS := 'D' 
      CURSOR STATUS_cur 
      IS 
        select ID_OBJ, ID_LOC, TYPE_, SHORTNAME 
        from N_OBJ_STORAGE WHERE STATUS IS NOT NULL; 
      st_rec       STATUS_cur%ROWTYPE; 
 
      -- Курсор наличия блокирующих сеансов. 
      -- Можно использовать параметризованный курсор (для OBJECT_NAME и TYPE) 
      CURSOR SESS_bl_cur 
      IS 
        SELECT distinct VS.SID, VS.SERIAL# 
        FROM V$SESSION VS, V$LOCK VL, DBA_OBJECTS OBJ 
        WHERE OBJ.OBJECT_NAME = 'ZZZ_TEST' AND 
        VL.TYPE='TO' AND 
        VL.ID1=OBJ.OBJECT_ID AND 
        VL.SID=VS.SID; 
 
      sess_bl_rec  SESS_bl_cur%ROWTYPE; 
 
      -- Курсор "убивания" подвисших KILLED-сеансов 
      CURSOR ORAKILL_cur 
      IS 
        SELECT '/bin/kill -s KILL ' || p.spid as Kill_cmd 
        FROM v$process p, v$session s 
        WHERE p.addr = s.paddr AND 
        s.status = 'KILLED'; 
 
      orakill_rec  ORAKILL_cur%ROWTYPE; 
 
      -- Перекомпилируем в цикле все INVALID-обьекты схемы SHOPS_MOD 
      CURSOR RECOMPILE_cur 
      IS 
         SELECT     'ALTER ' 
                  || DECODE (object_type, 
                             'PACKAGE BODY', 'PACKAGE', 
                             object_type 
                            ) 
                  || ' ' 
                  || owner 
                  || '.' 
                  || object_name 
                  || ' COMPILE' 
                  || DECODE (object_type, 'PACKAGE BODY', ' BODY', '') cmd, 
                  owner, object_name 
             FROM dba_objects OUTER 
            WHERE status = 'INVALID' 
              AND owner = 'SHOPS_MOD' 
              AND object_type IN 
                     ('PACKAGE', 
                      'PACKAGE BODY', 
                      'VIEW', 
                      'PROCEDURE', 
                      'FUNCTION', 
                      'TRIGGER' 
                     ) 
              AND (   object_type <> 'PACKAGE BODY' 
                   OR NOT EXISTS ( 
                         SELECT NULL 
                           FROM dba_objects 
                          WHERE owner = OUTER.owner 
                            AND object_name = OUTER.object_name 
                            AND object_type = 'PACKAGE' 
                            AND status = 'INVALID') 
                  ); 
 
      recompile_rec  RECOMPILE_cur%ROWTYPE; 
 
      l_alert_name   Varchar2(30)  := 'TM_RECREATE'; -- Имя сигнала 
      l_alert_msg    Varchar2(500) :=  
      'ВНИМАНИЕ: Для введения в строй нового Канала реализации, ' || Chr(13)  
       || 'необходимо сохранить данные и выйти из текущей формы расчета. ' || Chr(13) 
       || 'Через 3 минуты сеанс будет завершен!'; 
      la_smag        TStringTab; 
      l_strSQL       VARCHAR2(16000); 
      l_strSQL2      VARCHAR2(16000); 
      l_strSQL3      VARCHAR2(16000); 
      f_exit         BOOLEAN := false;   -- Выход:   по умолчанию - НЕТ 
      l_ret          NUMBER := 2;        -- Возврат: по умолчанию - Не было пересоздания 
 
    BEGIN 
 
-- Были ли измененные К.Р.? 
-- Используется как факт начала пересоздания (%NOTFOUND=FALSE) 
 
    OPEN STATUS_cur; 
    FETCH STATUS_cur INTO st_rec; 
    IF STATUS_cur%NOTFOUND THEN 
   f_exit:=true;     
    ELSE 
      f_exit:=false; 
    END IF; 
    CLOSE STATUS_cur; 
     
    -- Выход, если нечего перестраивать (2). 
    IF f_exit=true THEN RETURN l_ret; END IF; 
 
-- Да: были измененные К.Р. => Проверить, есть ли блокирующие сеансы. 
-- Получаем список "нехороших" сеансов. 
    -- Если есть нехорошие сеансы - разослать им сообщение о выходе,  
    -- ждать 3 минуты, переоткрыть. 
    -- Если после перечитывания еще живут - KILL SESSION, 
    -- а потом - прекращение процессов (нитей) 
 
    OPEN SESS_bl_cur; 
    FETCH SESS_bl_cur INTO sess_bl_rec; 
 
    IF SESS_bl_cur%FOUND THEN 
 
      -- Пошлем сообщение всем "считающим" сеансам 
      TM_UTIL.Alert_msg(l_alert_name, l_alert_msg); 
 
      CLOSE SESS_bl_cur; 
       
      -- Подождем 3 минуты... 
      DBMS_LOCK.SLEEP(180); 
 
      -- Перечитаем заново... кто же нас не послушался ;) 
      OPEN SESS_bl_cur; 
      LOOP 
        FETCH SESS_bl_cur INTO sess_bl_rec; 
        EXIT WHEN SESS_bl_cur%NOTFOUND; 
        -- 2.2. Убиваем "нехорошие" сеансы после перезапроса 
        l_strSQL :='ALTER SYSTEM KILL SESSION ''' || to_char(sess_bl_rec.SID) || ',' 
                    || to_char(sess_bl_rec.SERIAL#) || ''''; 
        EXECUTE IMMEDIATE l_strSQL; 
      END LOOP; 
 
      -- Убиваем KILLED-сеансы (если такие есть) для текущего ORACLE_SID 
      OPEN ORAKILL_cur; 
      LOOP 
        FETCH ORAKILL_cur INTO orakill_rec; 
        EXIT WHEN ORAKILL_cur%NOTFOUND; 
          -- Убить процесс (нить) зависшего KILLED-сеанса 
          shops_mod.rc(orakill_rec.Kill_cmd); 
      END LOOP; 
      CLOSE ORAKILL_cur; 
 
    END IF; -- По факту нехороших сеансов 
    CLOSE SESS_bl_cur; -- Для обоих случаев выхода 
 
-- Да, были измененные К.Р. и, возможно, убивали сеансы. 
-- Пересоздать нужные временные таблицы и их индексы 
 
    -- Удаляем временные таблицы (индексы удаляются автоматически) 
    l_ret:= 0; -- Как бы прогнозируем ошибку 
    EXECUTE IMMEDIATE 'drop table ZZZ_TEST'; 
 
    -- Создаем новые таблицы 
    -- Создадим набор новых "доступных" магазинов 
    SELECT SHORTNAME 
    BULK COLLECT INTO LA_SMAG 
    FROM N_OBJ_STORAGE 
    WHERE TYPE_='КР' AND INUSE=1; 
 
    -- Пересоздание ZZZ_TEST 
    l_strSQL  := 'CREATE GLOBAL TEMPORARY TABLE ZZZ_TEST (DATE_ DATE, ART VARCHAR2(30), ';  
    FOR j IN 1 .. la_smag.COUNT LOOP 
      l_strSQL  := l_strSQL  || '"' || la_smag(j) || '_п" NUMBER, '; 
    END LOOP; 
    l_strSQL  := l_strSQL   || '"Прод_маг" NUMBER) ON COMMIT PRESERVE ROWS'; 
    EXECUTE IMMEDIATE l_strSQL; 
 
    -- Пересоздание индексов 
    EXECUTE IMMEDIATE 'CREATE INDEX SHOPS_MOD.I_ZZZ_TEST ON SHOPS_MOD.ZZZ_TEST (ART)'; 
 
    -- Очищаем статус Каналов Реализации 
    EXECUTE IMMEDIATE 'UPDATE N_OBJ_STORAGE SET STATUS=NULL'; 
 
    -- Перекомпилируем в цикле все INVALID-обьекты схемы SHOPS_MOD 
    -- в т.ч. наиболее нужные для нас считающие пакеты 
    OPEN RECOMPILE_cur; 
    LOOP 
      FETCH RECOMPILE_cur INTO recompile_rec; 
      EXIT WHEN RECOMPILE_cur%NOTFOUND; 
        -- Выполнить сформированный ALTER XXX SHOPS_MOD.XXX COMPILE 
        EXECUTE IMMEDIATE recompile_rec.cmd; 
    END LOOP; 
    CLOSE RECOMPILE_cur; 
 
    -- 6. Итоговый Commit и возврат результата 
    COMMIT; 
    l_ret:=1; -- Было успешное пересоздание 
 
    RETURN l_ret; 
    END KP_Recreate_TEST; 
 
/*==========================================================================*/ 
END; 

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

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)

В следующем выпуске

Пока не знаю - все еще погружен в раздумья о судьбе рассылки и необходимости писать об Oracle на русском языке вообще...

С наилучшими пожеланиями,

  В.К., "некислый специалист по Oracle" :)