Jeremiah is correct. In addition, rollback segments are read as part
of the delayed block cleanout process, and it's not possible to predict
that, either.
- Jeremiah Wilton <jwilton_at_speakeasy.net> wrote:
> People also obtain read consistency data from the rollback segments
> after transactions have committed. If a query began before someone
> else's transaction committed, but continues reading, then needs the
> reconstruct the data from before the commit, in needs rollback data
> that is both committed and impossible to predict.
>
> I suppose if you could determine that the age of all undo entries in
> the portion of RBS that you will obliterate through shrinking are
> older than any query currently running in the database, then you
> could
> be sure that the shrink will not cause an ORA-01555.
>
> But the flaw in your logic is believing that once committed, rollback
> entries will not be needed for read consistency. They very well may.
>
> Because a query doesn't know what rollback entries it may need
> further
> down the road, you can't predict if your shrink will obliterate undo
> entries that a long-running query might need in the future. You keep
> asking if we can tell who is reading the rollback segments. The
> answer is that it doesn't matter. What you really need to ask is if
> we can tell who will need to read the rollback segments sometime
> soon.
> And you can't.
>
> --
> Jeremiah Wilton
> http://www.speakeasy.net/~jwilton
>
> On Tue, 26 Feb 2002, Glenn Travis wrote:
>
> > Hmmm. I think I CAN predict FUTURE needs of the rollback segments.
> > If there are transactions using the rollback space, users MAY need
> > it. If there are no transactions, then they won't. If I were able
> > to know who is reading from rollback, I would know if shrinking
> > might cause ORA-01555.
> >
> > Tell me if I'm off on this...
> >
> > Users will not read from the rollback segment unless they need
> > read-consistent data due to an open transaction against the data
> > they are looking for (thus reading the redo or undo info from
> > rollback). Otherwise they read from the data segments (committed
> > data).
> >
> > Oracle will not shrink the rollback segment if it contains open
> > transactions.
> >
> > So, if there are no users reading from rollback and I issue a
> > 'shrink' command, and it works, then the transactions are complete
> > and any user coming in after that will read from the data segments.
> >
> > If there are no users reading from rollback and I issue a 'shrink'
> > command, and it DOES NOT work, then the transactions are NOT
> > complete and any user coming in after that will read from the
> > rollback segments (the data is still there).
> >
> > If there ARE users reading from rollback and I issue a 'shrink'
> > command, and it works, then users run the risk of getting ORA-01555
> > (the data MAY be gone). Which is exactly why I asked my original
> > question ("How do I identify READERS of the rollback segments?") :)
>
>
> > > -----Original Message-----
> > > From: Jeremiah Wilton [mailto:jwilton_at_speakeasy.net]
> > >
> > > Since you cannot predict who might need to generate consistent
> reads
> > > from the RBS in the FUTURE, you cannot predict if you will cause
> > > ORA-01555 or not by shrinking.
> > >
> > > Your best bet is to get rid of people bloating up RBSs by
> limiting
> > > their growth, and enforcing the use of smaller transactions.
> That way
> > > you won't have to shrink so much.
> > >
> > > --
> > > Jeremiah Wilton
> > > http://www.speakeasy.net/~jwilton
> > >
> > > On Tue, 26 Feb 2002, Glenn Travis wrote:
> > >
> > > > Is there a way to tell if anyone is reading from the rollback
> > > > segments?
> > > >
> > > > I would like to manually issue 'alter rollback segment XXX
> shrink;',
> > > > but do not want to do so if there are users reading read
> consistent
> > > > data from the rollback space (thus giving them the ORA-01555
> error).
> > > >
> > > > Is there a way to check if the rollback segment is in use
> first?
> > > >
> > > > Can I try to take it offline? Will it fail if there is someone
> > > > reading from it?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jeremiah Wilton
> INET: jwilton_at_speakeasy.net
>
> 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!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Baumgartel
INET: treegarden_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).
Received on Tue Feb 26 2002 - 17:54:25 CST
