Re: Session problem with triggers and remote DB Link

do you commit after insert?

u even have to commit/rollback when you're doing selects over DBlinks

On 15 Nov 2001 01:31:03 -0800, fmas_at_hubwoo.com (fredericm) wrote:

>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

.......
We use Oracle 8.1.6-8.1.7 on Solaris 2.6, 2.7 boxes

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 750-3630
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email