ORA-01555: snapshot too old: rollback segment number %s with name "%s"
too small

PLEASE NOTE:

This article was written long, long ago (December 1997) for an
internal technical newsgroup for developers at my company.  The
explanation lacks some of the technical details of how Oracle
consistent read mechanism really works within the buffer cache, so
advanced readers, be aware.  The overall principle presented is,
nevertheless, valid and accurate. 
*******************

Problem: You are running a query that (for whatever reason) takes a
long time to complete. One or more of the tables you are querying is
being changed by other users while you perform your query.  After some
time, you receive the ORA-01555, and your query fails.

Explanation: 

When you perform a query, Oracle knows the point in time (called a
system change number or SCN) at which your query began.  In order to
give you a consistent set of data, Oracle needs to show you the data
the way it looked exactly when you began your query.

Meanwhile, people are changing the underlying data being selected by
your query.  These other transactions are actually changing the data
on the datafiles.  As a part of their transaction, they record the
data in the rollback segments the way it looked before changing, in
case they need to perform a rollback before they commit.  This data
comes in handy for you, because it lingers around in the rollback
segments even after the other transactions have committed.  While your
query is scanning through a datafile selecting data, it might come
upon a piece of data that is newer than the query you are performing.
At this point it goes off and checks for this data in the rollback
segments. Usually it finds it, and moves on.

The problem is that Oracle doesn't know what your long-running query
might need in the future.  In a very transactional environment, the
rollback segments are changing constantly, and fairly rapidly.  It is
possible that the piece of old data that you need from the rollback
segments will have already been overwritten by some other transaction
before you get to it.  When your query encounters this situation, it
generates an ORA-01555 and fails.

Solutions:

1. Avoid querying rapidly changing data over a long period of time.
If you have a query that is going to run for a long time, and which
selects a long time period of data (such as a month), Try segmenting
your query into two parts: Everything but the past four days, and just
the last four days.  Remember that in a query that selects a whole
month, you spend hours grinding through the fairly quiescent data from
the first part of the month, and only much later get down to the newer
stuff (which, incidentally, is changing heavily). Segmenting in this
way could very well solve your problem.  This is also probably why
month end reports eventually run successfully, once the month end is a
few days past. At that point, the data for the 29th and 30th is no
longer being heavily changed.

2. Increase the number of rollback segments, in hopes that you will
get lucky and nobody will overwrite the data you are planning to
select somewhere down the road.  This isn't a very good solution,
because it probably won't work on a very transactional database.

3. Maintain a read-only snapshot database instance in which certain
tables from the OLTP instance are refreshed at some interval. This
would be useful, but implementation is a little far away right now.

4. Tune the queries to complete faster, reducing the possibility that
data will change out from under the query, and eventually become
unavailable.