Jared,
They are many causes for the famous "ORA-1555 Snapshot
too old", one of them is fetch across commit. It is
when you're commiting and fetching the same data. it
is not accept in ANSI SQl but it is by Oracle.
In numerous place, developpers have complained that I
sized the rbs too small because of the ORA-1555.
Suddenly my rbs were ok after moving the commit
outside the loop .
- Jared Still <jkstill_at_cybcon.com> a écrit : >
> Stephane,
>
> This doesn't look like it will cause ORA-1002, at
> least
> I don't see it.
>
> What's the relationship between ORA-1002 and
> ORA-1555?
>
> I got up rather early with a headache this morning,
> so maybe I'm
> just not thinking clearly yet. :)
>
> Jared
>
> On Wednesday 09 May 2001 05:40, paquette stephane
> wrote:
> > My observation is not on the elegancy of the code
> but
> > why commit at 100 rows ? Are you updating 10 000
> 000
> > rows ?
> > I've seen a lot of ORA-1555 because of fetch
> across
> > commit.
> >
> >
> > --- Diana_Duncan_at_ttpartners.com a écrit : >
> >
> > > Linda,
> > >
> > > Might I suggest avoiding the "elegant" looping
> and
> > > try some "inelegant"
> > > looping? It should be faster, although I can't
> make
> > > any promises.
> > >
> > > Warning, untested, and you can probably do
> better
> > > than an in() -- but it
> > > should give you the gist...
> > >
> > > begin
> > > loop
> > > update reg.docalert_responses_at_ncp
> > > set campaign_response_handled = 1
> > > where campaign_response_handled != 1
> > > and rownum <= 100
> > > and docalert_response_id in (select
> > > docalert_response_id
> > > from docalert_emails_050401_at_ncc
> > > where sent = 1);
> > > commit;
> > > exit when sql%notfound;
> > > end loop;
> > > end;
> > > /
> > >
> > > Diana Duncan
> > > TITAN Technology Partners
> > > One Copley Parkway, Ste 540
> > > Morrisville, NC 27560
> > > VM: 919.466.7337 x 316
> > > F: 919.466.7427
> > > E: Diana_Duncan_at_ttpartners.com
> > >
> > >
> > >
> > >
> > >
> > > "Hagedorn,
> > >
> > >
> > > Linda" To:
> > > Multiple recipients of list ORACLE-L
> > > <ORACLE-L_at_fatcity.com>
> > > <lindah_at_epocr cc:
> > >
> > >
> > > ates.com> Fax to:
> > >
> > >
> > > Sent by:
> Subject:
> > > Pl/sql loop assistance
> > >
> > > root_at_fatcity.
> > >
> > >
> > > com
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > 05/08/2001
> > >
> > >
> > > 02:47 PM
> > >
> > >
> > > Please
> > >
> > >
> > > respond to
> > >
> > >
> > > ORACLE-L
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Hello,
> > >
> > >
> > > I'm having difficulty coding this loop and am
> hoping
> > > someone can see how
> > > this can be done.
> > >
> > >
> > > I have two tables, one on each instance
> > > reg.docalert_responses_at_ncp and
> > > reg.docalert_emails_05040_at_ncc
> > >
> > >
> > > The requirement is to set
> > >
> ncp.reg.docalert_responses.campaign_response_handled
> > > = 1 for all
> > > docalert_response_id's that exist in
> > > ncc.reg.docalert_emails_050401 where
> > > sent=1. Update 100 at a time and commit. The
> join
> > > column,
> > > docalert_response_id appears in both tables.
> > >
> > >
> > > I know I can set autocommit, but I'd really like
> to
> > > see the elegant loop
> > > logic. The DBLinks are in place.
> > >
> > >
> > > Any assistance is appreciated.
> > >
> > > Thanks, Linda
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author:
> > > INET: Diana_Duncan_at_ttpartners.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> >
> >
>
> >
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> > =====
> > Stéphane Paquette
>
=== message truncated ===
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Pour faire vos courses sur le Net,
Yahoo! Shopping :
http://fr.shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed May 09 2001 - 10:37:22 CDT
