Почему oracle возвращает ошибку ORA-02046?

Question

[Николай Бараненко]

Здравствуйте.

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

в процедуре calculate_1 на удаленном сервере была добавлена проверка значения PARAMETER_ID на null или не null, для маскирования вероятных исключений отсутствия ID в справочнике

OPEN CALCULATE_PARAMETER; 
    LOOP
      FETCH CALCULATE_PARAMETER INTO CALCULATE_PARAMETER_ARRAY; 
      EXIT WHEN CALCULATE_PARAMETER%NOTFOUND;  
      if (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is null) then CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE:=0; end if;
      IF (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is not null)
        THEN
    /* ADDED from this */
        PARAMETER_ID:=null; 
        BEGIN
     EXECUTE_TEXT:='select t.PARAMETER_ID from SERVERS_PARAMETERS_DIR@BMCDB t '||
    'where '||
    't.server_id IN (select server_id from servers_dir@BMCDB a where upper(a.location) like upper(''%Moskow%'')) '||
    'and '||           
    't.parameter_name=:PARAMETER_NAME';
       EXECUTE IMMEDIATE EXECUTE_TEXT INTO PARAMETER_ID USING CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME;
          EXCEPTION
          when OTHERS then begin /*dbms_output.put_line(PARAMETER_NAME);*/ goto PARAMETER_ID_SELECT; end;
          end;     
          <<PARAMETER_ID_SELECT>> 

        if (PARAMETER_ID is not null)
        THEN  
/* to this */
        insert into SERVERS_PARAMETERS@BMCDB
        (PARAMETER_VALUE_ID, PARAMETER_ID, PARAMETER_VALUE, DATE_CURRENT_CHECK)
        values
         (SERVERS_PARAMETERS_ID.nextval@BMCDB,

    (select PARAMETER_ID from SERVERS_PARAMETERS_DIR@BMCDB t
    where
    t.server_id IN (select server_id from servers_dir@BMCDB a where upper(a.location) like upper('%Moskow%'))
    and
    t.parameter_name=CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME),
         CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE,
         sysdate);
        commit;
        END IF;
        END IF;
    END LOOP;

далее при попытке выполнить

begin
calculate_1@db_link1;
calculate_2@db_link1;
calculate_3@db_link1;
end;

oracle стал возвращать ошибку

ORA-02046: Distributed transaction already begun

Почему oracle возвращает эту ошибку и порекомендуйте, что нужно сделать, чтобы эту проблему решить?

p.s.

1. версия Oracle - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. select * from dba_2pc_pending;
возвращает no rows
3. процедуры на удаленной БД calculate_1, calculate_2, calculate_3 валидны.

Answer 1

[Smithson]

У вас в задаче стоит LOOP, а цикл может выполняться долго.
Ошибка ORA-02046 означает, что предыдущее выполнение еще не закончено.
Стандартное решение поставить до цикла команду set transaction read only, но в вашем случае, когда вы используете insert, это не пройдет.

Comments

[Николай Бараненко]

какие есть варианты для insert?

[Smithson]

Николай Бараненко: Решения в общем виде я не знаю. Могу подсказать возможный хак - блокировка распространяется на физическую таблицу. Если у вас партиционированная таблица, причем так, что разные insert будут попадать в разные партиции - тогда сможете выкрутится. Но это весьма хлипко и не тривиально.

Answer 2

[Draconian]

Сколько ж там у вас параметров, что такие простые селекты и инсерты долго выполняются?

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

Comments

[Николай Бараненко]

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

[Draconian]

Николай Бараненко: 3300 - это прилично. Но проблема, как мне кажется, не с кодом, а с БД.
Есть дока ( docs.oracle.com/cd/B10501_01/server.920/a96521/ds_txns.htm#99 ) по распределенным транзакциям, отслеживайте выполнение процедуры, смотрите, есть ли транзакции в DBA_2PC_PENDING, проверяйте параметр DISTRIBUTED_TRANSACTION в конечной БД.