> 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: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > 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" <[EMAIL PROTECTED]> > 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:[EMAIL PROTECTED]] > > Reply To: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
