I don't have a definitive answer for that. My guess would be that 'compute' would be required so that all blocks are touched.
Another way of dealing with delayed block cleanouts is to do a 'select * from table;'. As long as you are going to touch every block anyway, you might as well compute the stats. But now I'm speculating. :) Jared Walter K <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/25/02 11:20 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: ORA-01555 Mystery (Help) Jared, would you elaborate more on this? Does this need to be a 'compute' or can it be an 'estimate' on the analyze? I read the info on Steve's site as suggested by Barb and it sounds like block cleanout may be the issue but I'm still trying to digest the concept/issue as it relates to my circumstance. For the others that have contributed to the thread, yes, the table is definitely locked in exclusive mode (via a different session) before the SELECT is performed and the lock is not released until the following day. I too was suspicious that the lock was accidentally being released. -w --- [EMAIL PROTECTED] wrote: > Delayed block cleanouts can still cause the > ORA-1555, even > after locking the table in exlusive mode. > > That's the purpose of the analyze, to force the > block cleanouts. > > Jared > > > > > > > Paul Baumgartel <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 01/25/02 09:30 AM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject: RE: ORA-01555 Mystery (Help) > > > Sure, but the original post concerns a *query*, not > a transaction, and > before running the query, the user locked the > queried table in > exclusive mode, to ensure that no other session > could write to the > queried table. How do we account for the query's > need to read from > rollback? > > > --- "Baker, Barbara" > <[EMAIL PROTECTED]> wrote: > > > > 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 > > > > > > > __________________________________________________ > . > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: > 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). __________________________________________________ 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: 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).