> Dick:
> This makes the assumption that Walter can get to the code, find out what
> it's doing, and make modifications. (In our case, we can't. The code is
> vendor-supplied, unchangeable, and is written in Cobol).
>
> It also sounds like this might be happening in the middle of the night.
> I'd guess there's a limit to how much information Walter can gather about
> what happened 5 hours into the job at 3:00 am
>
> If he's desperate to get the data loaded and he can't change the sql, then
> his options are limited.
>
> I believe all the possible causes for 1555 errors have been listed in this
> thread. Hopefully he can identify which is causing the grief and find a
> resolution.
>
> Barb
>
>
> ----------
> From: dgoulet_at_vicr.com[SMTP:dgoulet_at_vicr.com]
> Sent: Friday, January 25, 2002 11:09 AM
> To: Baker; Barbara; Multiple recipients of list ORACLE-L
> Subject: Re:RE: ORA-01555 Mystery (Help)
>
> Barb,
>
> I've tried Steve's idea in the past and although it sorta fixed
> the problem
> with the large batch job, it created problems elsewhere. It also
> did not
> totally fix the problem when other applications updated parts of the
> table(s)
> and committed their transaction. Many folks believe that by
> allocating a large
> rollback segment to their session they have fixed the problem.
> Wrong, this
> particular issue can be caused by your own application plus anyone
> else who is
> using the database and other rollback segments.
>
> The real issue here is to either find out who or what is
> updating the
> underlying table or else speeding up the process. There were two
> points that I
> found easy to implement that fixed 90% of our errors.
>
> 1) Don't commit across a cursor. In this scenario look for
> cases where your
> pulling data from a table, updating that table, and then continuing
> to read data
> from the cursor. This one will pop a 1555 very regularly since the
> cursor
> depends on a read consistent view, but you just released the
> rollback segments.
>
> 2) Use an order or group by in the select statement. This one
> sounds odd,
> but it does work. By placing either an order by or group by clause
> in the
> select statement you force Oracle to read all of the data at one
> time, place it
> in a temp segment, and then hand it over. The end result is that
> when the first
> row of data appears in your application you no longer need any
> rollback to
> create a read consistent view. If your just pulling from the table,
> then Oracle
> hands over a row as it satisfies the query criteria. OH, did you
> just update
> and commit a change? Well that is NOT going to be included in your
> result set
> since it is already locked in concrete.
>
> Try one of these & see if it fixes your problem.
>
> Dick Goulet
>
> ____________________Reply Separator____________________
> Subject: RE: ORA-01555 Mystery (Help)
> Author: "Baker; Barbara" <bbaker_at_denvernewspaperagency.com>
> Date: 1/25/2002 8:52 AM
>
>
> I have a batch job that does this consistently. It's the only job
> in the
> database; it sets the transaction to a hugh rollback segment. And
> it eats
> its own tail.
>
> Depending on how the job is written, it may need a read consistent
> view
> itself (as opposed to some other query in the database needing that
> read
> consistent view.) In that case, it may well go try to read its
> own
> rollback segment, only to find that it's been overwritten. (Oddly
> enough,
> even when there's plenty of space to extend the rollback, Oracle
> will decide
> to overwrite the original rollback segments rather than extend if it
> thinks
> it doesn't need those segments any more.)
>
> I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
> places an uncommitted transaction in your rollback segments for the
> length
> of the run. This will guarantee that the rollback segments don't
> get
> overwritten.
> Good luck!
>
> Barb
>
> > ----------
> > From: Walter K[SMTP:alden14004_at_yahoo.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Friday, January 25, 2002 9:15 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: ORA-01555 Mystery (Help)
> >
> > Hi,
> >
> > A user in our data warehousing group is running into
> > the old ORA-01555 (snapshot too old) error every time
> > she runs a massive (20 million rows) select against
> > one table via a view. I confirmed that the view only
> > translates to the one table.
> >
> > The user swears that no one would be making any
> > updates/deletes to the table she is selecting from. I
> > suggested she lock the table in exclusive mode, prior
> > to running her massive select to guarantee no one else
> > could change the data in the table and cause the
> > triggering of the 1555 error. Locking the table was a
> > viable option because it's a staging table in the
> > warehouse itself. She locked the table in exclusive
> > mode last night and it locked; fired off her query,
> > and it failed 5 hours later with the 1555 error again.
> >
> > I'm stumped on this. I just don't see how this is
> > possible. Any suggestions?
> >
> > Thanks!!!
> > -w
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Great stuff seeking new owners in Yahoo! Auctions!
> > http://auctions.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Walter K
> > INET: alden14004_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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bbaker_at_denvernewspaperagency.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).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
INET: bbaker_at_denvernewspaperagency.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 Fri Jan 25 2002 - 12:56:41 CST
