Donald K. Burleson Oracle Tips

Using unlimited extents in Oracle objects

Using Oracle’s MAX EXTENTS UNLIMITED option (in releases 7.3 and above), has been a godsend for many Oracle DBS’s. but there are some serious problems with using this option.

1. Most space management requires the 'ST' enqueue, and Oracle has only a single 'ST' enqueue for space management operations. Allocating & freeing extents use this enqueue which can become a point of contention when lots of space management is occurring.

2. Regardless of the setting for MAXEXTENTS, rollback segments have an internal maximum of 32767 extents. Attempting to extend a rollback segment beyond this may result in internal errors that can corrupt your database.

Oracle suggests the following rules to avoid problems:

1. NEVER use 'MAXEXTENTS UNLIMITED' for objects OR default storage clauses. 

2. Only use values of MAXEXTENTS above the limits listed above if this is required. Eg: If an object is at 121 extents with a block size of 2K it may be sensible to increase MAXEXTENTS to 130 along with any other change to the extent sizing to ensure these extra extents take a reasonable length of time to fill. 

3. Never change the storage clause of SYS objects unless the change is both needed and supported. 

4. Use the 'TEMPORARY' tablespace feature in Oracle 7.3 and higher but make sure you set sensible sizes for the NEXT default storage attribute of the TEMPORARY tablespace as there is no way to control MAXEXTENTS of such sort segments. 

5. Set Parameter - UNLIMITED_ROLLBACK_SEGMENTS to FALSE and avoid using unlimited extent format for rollback segments.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm