I was almost ready to subscribe to the idea of delayed cleanout, but I cannot understand why really. The necessity for reading a block from the rollback segments comes from encountering during the course of the SELECT a block the SCN of which is higher than the SCN when the query started. I have of course no certainty about it, but it would be logical to expect the block's SCN to be properly set irrespectively of the clean-out being immediate or delayed. In other words, even if a SELECT physically writes blocks, it should not have anything to do with rollback segments anyway. I share Mladen's opinion, somebody must be economical with the truth somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you really sure that the code contains no 'just in case' commit ou rollback which would release the lock? And by the way, 5 hours look to me like an awfully long time, even for a 20 million row mega-select of death.
[EMAIL PROTECTED] wrote: > > Precisely the point I was trying to make, when I put the question if it was > a normal select, or if it was within a PL/SQL block? The myth is that > snapshot too old happens only when some other transaction was in the > process of performing an DML on a table, when you did a select on it. It > can happen for other reasons too. Search on Metalink for "Delayed block > cleanouts" and "fetch across commits". > > Raj > > "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on > 01/25/2002 11:52:05 AM > > Please respond to [EMAIL PROTECTED] > > Sent by: [EMAIL PROTECTED] > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > > 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: Stephane Faroult 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).
