ORA-03113. TRIGGER IS NOT RELIABLE?

From: David Cheung <davidcheung83_at_hotmail.com>
Date: Sat, 16 Oct 1999 00:00:51 -0700
Message-ID: <380822A3.2DCC41D2@hotmail.com>

Hi there,

I met a terrible situation when creating autonomous trigger for a table on a node to be consistent with another table on another node. Some times it's okay, but sometimes I met the ORA-03113 erros from the Sql*plus as well as ORA-07445 in the alert log file.

The Oracle server version is 8.1.5 on Solaries2.6. I employed the following procedure:
Step A. Set up a database base link on node 1 to node 2:

        create database linke node1to2
        connect to scott identified by tiger
        using 'node1to2';

----The database link is tested to be ok when trying to select tables in node 2 on node 1.
Step B.

        create or replace trigger emp_trigger
        before update or delete on emp
        for each row
        declare
          pragma autonomous_transaction;
        begin
          if inserting then
             insert into emp_at_node1to2 values (:new.empno, :new.ename);
             commit;
          end if;
          if deleting then
             delete from emp_at_node1to2 where empno=:old.empno;
             commit;
          end if;
      end;

 ---the compilation without errors.
Step 3 Running to test.

Some times it's okay, that is, the two tables can be inserted /deleted at the same time. However some times we face the terrible error messages: ORA-03113 and ORA- 07445.

Your help will be highly appreciated.
David

>
Received on Sat Oct 16 1999 - 02:00:51 CDT