RE: Sizing rollback segments

From: Bobak, Mark <>
Date: Fri, 9 May 2008 11:17:07 -0400
Message-ID: <>


Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059

ProQuest...Start here.

-----Original Message-----

From: [] On Behalf Of Schauss, Peter Sent: Tuesday, May 06, 2008 5:03 PM
Subject: Sizing rollback segments

This is for Oracle

I have a data warehouse database which I have inherited on which we run an ETL every hour. It currently has 14 rollback sized:

  • initial_extent = 5 mb
  • next extent 5 mb
  • min extents 11

After the database has been up for a day the number of extents on each segment ranges from 34 to 295. Right after the database was restarted a few days ago several of the users' reports returned with ORA-01555 errors. The problem seems to have gone away now that the rollback segments have increased in size.

So my question is should I increase min_extents to 34 in hopes of minimizing the ORA-01555 errors on startup? I note that Oracle recommends setting this parameter no higher than 20, making some vague reference to their own testing. Does this mean that I should think about rebuilding the rollback segments at a larger size, 10 mb for example?

Given that we run ETL's every hour I have not seen the segments shrink appreciably so I suspect that the optimal parameter is probably not going to be relevant in my situation.

Any suggestions welcome.

Peter Schauss

-- Received on Fri May 09 2008 - 10:17:07 CDT

Original text of this message