Re: trigger- problem: too many open cursors

From: ddf <oratune_at_msn.com>
Date: Wed, 1 Apr 2009 05:48:46 -0700 (PDT)
Message-ID: <c5b7fcdd-fedc-4273-82a6-2e40b41d5f69_at_e2g2000vbe.googlegroups.com>



On Apr 1, 7:00 am, Andreas Mosmann <mosm..._at_expires-30-04-2009.news- group.org> wrote:
> hi ng,
>
> it is a bit difficult to explain and more difficult to analyze and we
> look for a hint where to search for our problem.
>
> We use Oracle 11.1.0.6.0 and there are triggers, that write down changes
> of some tables compressed into a special changelog-table.
>
> Something like
>
> CREATE TRIGGER MyOwner.MyTrigger BEFORE UPDATE
> ON TABLE1
> FOR EACH ROW
>
> declare
>       EOraDatumAuszerhalb EXCEPTION;
>       PRAGMA EXCEPTION_INIT(EOraDatumAuszerhalb, -20100);
>
>     lvs_ChangeLog_Fields varchar2(4000);
>     lvs_ChangeLog_Program varchar2(64);
>     lvs_ChangeLog_IPAdress varchar2(64);
>     lvs_ChangeLog_Machine varchar2(64);
> begin
>    -- some probably uninteresting code
>       if (:new.cdatumfaellung>sysdate) or
>          (:new.cdatumpflanzung>sysdate) or
>          (:new.cdatumfaellung<:new.cdatumpflanzung) then
>         raise EOraDatumAuszerhalb;--exception
>       end if;
>     lvs_ChangeLog_Fields:=
>         EncodeXML('CID',:old.CID,:new.CID)||
>
> EncodeXML('CIDNEBENANLAGE',:old.CIDNEBENANLAGE,:new.CIDNEBENANLAGE)||
>         EncodeXML...
>     ;
>     if lvs_ChangeLog_Fields is not null then
>
> lvs_ChangeLog_Fields:='<Spalten>'||chr(13)||lvs_ChangeLog_Fields||'</Spalte­n>'||chr(13);
>
>         select
>             program,
>             machine,
>             sys_context('USERENV','IP_ADDRESS')
>         into
>             lvs_ChangeLog_Program,
>             lvs_ChangeLog_Machine,
>             lvs_ChangeLog_IPAdress
>         from
>             v$session
>         where
>             audsid=sys_context('USERENV','SESSIONID');
>
>         insert into BAUMPRG.TBCHANGELOG(
>             clogid,
>             clogtime,
>             clogtable,
>             CLOGIDUSER,
>             CLOGOSUSER,
>             CLOGPROGRAM,
>             CLOGMACHINE,
>             CLOGIPADDRESS,
>             clogschema,
>             clogreason,
>             cdataid,
>             cdatafields
>         )values(
>             BAUMPRG.PChangeLog.NewGUID(),
>             sysdate,
>             'TABLE1',
>             :new.CIDNUTZER,
>             SYS_CONTEXT('USERENV','OS_USER'),
>             lvs_ChangeLog_Program,
>             lvs_ChangeLog_Machine,
>             lvs_ChangeLog_IPAdress,
>             'OWNER',
>             'U',
>             :old.cid,
>             lvs_ChangeLog_Fields
>         );
>     end if;
> end;
>
> This is only a Code fragment.
> EncodeXML is a short function, that returns an XML- string- fragment
> only if there are differences between old and new value.
>
> Now the problem: Sometimes (no way to find out all circumstances) there
> is an error
>
> ORA-01000: maximum open cursors exceeded
> ORA-06512: at MyOwner.MyTrigger, line 167
> ORA-04088: error during execution of trigger MyOwner.MyTrigger
>
> Line 167 is the line, where the insert statement is executed
>
> There are no loops in the code and so we do not know where new cursors
> are opened. What can we do to find out our mistake(s)?
>
> Many thanks
> Andreas
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

I doubt that there is a mistake, except in the concept that all cursors are explicit cursors you've coded. Oracle can open additional cursors to aid in processing your query, and it appears that at times you have a heavier than 'normal' user load consuming the cursors your trigger normally uses.

I suggest you increase the value of the open_cursors parameter; you can double this value and it won't affect memory until these cursors are needed.

David Fitzjarrell Received on Wed Apr 01 2009 - 07:48:46 CDT

Original text of this message