I think you might have to throw a full hint in there to insure you're really touching all the blocks (select /*+ FULL*/ count(*) from table_name).
But Jared's correct. If you're gonna go to that much trouble, might as well compute statistics. > ---------- > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] > Reply To: [EMAIL PROTECTED] > Sent: Friday, January 25, 2002 3:05 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-01555 Mystery (Help) > > 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). > -- 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).
