Re: ORA-01555 Mystery (Help)

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.

Rajesh.Rao_at_chase.com 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" <bbaker_at_denvernewspaperagency.com>@fatcity.com on
> 01/25/2002 11:52:05 AM
>
> Please respond to ORACLE-L_at_fatcity.com
>
> Sent by: root_at_fatcity.com
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: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
> >

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