Session problem with triggers and remote DB Link

Hi,

I've 2 DB named DB1 and DB2.
In DB1, a table with a triggers witch fire and insert in a table of DB2 each time it receive a row:
here is the trigger code:

create or replace trigger SendMessageToHub after insert on zca_trackpo
for each row
declare

 tid char(24);

begin

 tid := :new.arfcipid||:new.arfcpid||:new.arfctime||:new.arfctidcnt;

 insert into zca_bbpmessage_at_HUBDB
   values (tid, :new.doc_number, :new.timestamp, :new.logsys, :new.mess_type, 'I', :new.zog_cust, :new.service);

exception

 when others then
 update zca_trackpo_shadow set status = 'E'

   where arfcipid   = :new.arfcipid 
     and arfcpid    = :new.arfcpid
     and arfctime   = :new.arfctime
     and arfctidcnt = :new.arfctidcnt; 

end SendMessageToHub;
/

When DB2 table is updated a trigger is fired to update DB1 table here is the trigger code:

create or replace trigger AckMessageToBbp after delete or update of status on zca_bbpmessage for each row
declare

 tid char(24);

 p_arfcipid   char(8);
 p_arfcpid    char(4);
 p_arfctime   char(8);
 p_arfctidcnt char(4);

begin

 tid := :old.tid;

 p_arfcipid   := substr(tid,1,8);
 p_arfcpid    := substr(tid,9,4);
 p_arfctime   := substr(tid,13,8);
 p_arfctidcnt := substr(tid,21,4);
  

 update zca_trackpo_shadow_at_BBPDB set status = 'Z'

    where arfcipid   = p_arfcipid 
      and arfcpid    = p_arfcpid 
      and arfctime   = p_arfctime 
      and arfctidcnt = p_arfctidcnt;

exception

 when others then
 raise_application_error(-20020, 'TRIGGER_ERR : ' || tid );

end AckMessageToBbp;

The DATABASE LINK DEF ARE:
create public database link BBPDB
connect to DB1 identified by DB1
using 'DC1.world';

create public database link HUBDB
connect to DB2 identified by DB2
using 'hubdevdb.world';

THE PROBLEM: If you launch several insert on DB1 table, the trigger insert well lines in DB2 table but DO NOT CLOSE ALWAYS it's session, so after 250 insert, we have nearly 80 session open on DB2 !!!! They're inactive but keep here. If you continue to insert, number of session and process grown until no more free are available and server hang !!!

In the other way if you update several times THE DB2 table, the triggers fire and DB1 is updated and all is ok, there is no escavation of session in DB1.

DB1 is SAPR3 oracle 8.1.6 DB and DB2 is oracle 8.1.6 DB

If any can help me, that will be great.

Do I must use an "alter session close database link linkname;" each time the trigger fire ?

Best regards
Fred Received on Thu Nov 15 2001 - 03:31:03 CST