Re: What does "rollback segment too small" mean?

In article <MIKEW.95Feb8104229_at_gopher.dosli.govt.nz> Mike Williams, mikew_at_gopher.dosli.govt.nz writes:
> I help administer a host that is getting a rash of ORA-01555 errors
>
> 01555, 00000, "snapshot too old (rollback segment too small)"
> // *Cause: rollback records needed by a reader for consistent read
 are
> // overwritten by other writers
> // *Action: Use larger rollback segments
>
> during a long-running query. We have 3 tablespaces:

Think of a rollback segment as being a circular list of extents that are used by updaters to store their transactions for rollback purposes.

Extent 1 | Extent 2 | Extent 3

Once Extent 3 is full, ORACLE will re-use Extent 1, as long as there are no active transactions in it. If there are , ORACLE will create Extent 4, etc. A query does not count as an active transaction, but it may need rollback segment information to reconstruct the read-consistent view of the data.

So, if you have along running query, and other processes are doing enough updates that cycle through your rollback segment extents, the updaters will overwrite data needed by your query, and you will get the 'snapshot too old' message.

For example, suppose that you had a large employee table. You issue the statement 'SELECT EMPLID, SALARY FROM EMPLOYEE;'. At the same time, someone is running a program that opens a cursor to read all of the employee records to update the salary, doing occasional commits along the way. The update begins with Extent 1, and finally works its way through Extent 3. Because it committed, the transactions that it wrote to Extent 1 are no longer active, so the ORACLE will re-use Extent 1. However, your SELECT statement needed the information in Extent 1 to recreate the original value of SALARY.

You have a couple of options

1. Make sure there aren't any update jobs running when your query is running
2. Make your rollback segments large enough that your query finishes before the updates cycle through the rollback segment.

There are probably other options, but that is all that comes to mind at the moment.

Hope this helps.

Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN