Re: Rollback Segments

Hi Carlos!

"Snapshot too old .." means that you have a long running query or read-only transaction which uses data being changed by other transactions while it's running. Oracle uses rollback segments to retrieve older values (to give you consistent view of data). After transaction commits its rollback entries may be used for this purpose until another transaction need a place in this rollback segment.
  So, let's say you have a long-running query which - among others - uses data from table X. While it is running another transaction changes some data in table X and commits. The older values (before change) are in the rollback segment and query can still reach it. But after some time another transaction can reuse this place in rollback segment. Then, if your query (which is still running) needs to get the previous values of some rows in X you will get "Snapshot too old ..." message.

   The simplest solution is to lock in exclusive mode all the tables your query uses. But I know it can be unacceptable because it would stop all transactions that want to modify these tables. So, probably, the best thing you can start with is to make your rollback segments generally biger. The values you gave seem very small to me. I'd suggest for example 16 rollback segments like this:
initial 1M next 1M minextents 10 optimal 10M in a 200M tablespace (divide it by something if you can not afford so much).

-> my advice: always use maxextents or optimal or fill the tablespace
tightly with segments!

Darek Received on Tue Aug 18 1998 - 03:43:52 CDT