Re:RE: RE: ORA-01555 Mystery (Help)

HUMMM, Sounds just like a certain PeopleSoft Cobol process we have. Am there, doing that, and just as frustrated. Damned third party software vendors!! If only they would admit that these errors can occur.

Dick Goulet

____________________Reply Separator____________________
Author: "Baker; Barbara" <bbaker_at_denvernewspaperagency.com>
Date:       1/25/2002 10:39 AM

> 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.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).