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 <alden14004_at_YAHOO.COM>
Sent by: root_at_fatcity.com
01/25/02 11:20 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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
- Jared.Still_at_radisys.com 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 <treegarden_at_YAHOO.COM>
> Sent by: root_at_fatcity.com
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> 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"
> <bbaker_at_denvernewspaperagency.com> 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: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:
> INET: Jared.Still_at_radisys.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).
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: alden14004_at_yahoo.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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Jared.Still_at_radisys.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).
Received on Fri Jan 25 2002 - 16:13:34 CST
