Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Donald K. Burleson

Oracle Tips  

Number and Placement of Rollback Segments

The longer a transaction, the larger the rollback segment it will require. One is automatically created when the database is created. This initial rollback segment is for SYSTEM tablespace use. If you have plans for more than one tablespace, you will need a second rollback segment. Of course, this second segment will have to be created in the SYSTEM tablespace. Once the ROLLBACK tablespace is defined, and additional rollback segments are created, the second rollback segment in the SYSTEM tablespace should be placed offline or dropped.

Each rollback segment must be created with a MINEXTENTS value of at least 2 and a MAXEXTENTS based on the number of rollback segments in the tablespace, the size specified for each extent, and the size of the ROLLBACK tablespace. Each of the extents should be the same size; that is, initial should equal next, and pctincrease has to be set to 0 percent (look at the STORAGE statement specification in Appendix B in the download area for an explanation of these parameters). If you intend to do large batch transactions, it may be advisable to create a large rollback segment used only for batch operations. This single large segment can be left offline until needed, then activated and used for a specific transaction using the SET TRANSACTION USE ROLLBACK SEGMENT command.

If you opt to use the UNDO tablespace in Oracle, rather than the traditional rollback segments, make sure you size it according to the required transaction load and the desired retention time for flashback queries (more on this in the section on tuning undo tablespaces in Chapter 12).

This is an excerpt by Mike Ault’s book “Oracle Administration & Management”.  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.