Re: Optimal settings for rollback segs.

******Oracle7 Server Concepts Release 7.2, Page 3-22 reads*******

How Extents Are Deallocated from a Rollback Segment

When you create or alter a rollback segment, you can use the storage parameter OPTIMAL, which applies only to rollback segments, to specify the optimal size of the rollback segment in bytes. If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, Oracle compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger than its optimal size and the extents immediately following the extent just filled are inactive, Oracle deallocates consecutive non-active extents from the rollback segment until the total size of the rollback segment is equal to or as close to but not less than its optimal size. Oracle always frees the oldest inactive extents, as these are the least likely to be used by consistent reads. A rollback segment's OPTIMAL setting cannot be less than the combined space allocated for the minimum number of extents for the segment:

(INITIAL + NEXT + NEXT + ... up to MINEXTENTS) bytes

When you drop a rollback segment, Oracle returns all extents of the rollback segment to its tablespace. The returned extents are then available to other segments in the tablespace.

********Oracle Server SQL Reference Release 7.2, Page 4-45 reads********

ALTER ROLLBACK SEGMENT rollback_segment SHRINK _________________
                                               |_TO integer_____


                                                            |_K_

                                                            |_M_

Keywords and Parameters

rollback_segment specifies the name of an existing rollback segment.

SHRINK              attempts to shrink the rollback segment to an optimal or
                    given size.  
                                 

Usage Notes

The SHRINK clause of the ALTER ROLLBACK SEGMENT command initiates an attempt to reduce the specified rollback segment to an optimum size. If size is not specified, then the size defaults to the OPTIMAL value of the STORAGE clause of the CREATE ROLLBACK SEGMENT command that created the rollback segment. If the OPTIMAL value was not specified, then the size defaults to the MINEXTENTS value of the STORAGE clause. The specified size in a SHRINK is valid for the execution of the command; thereafter, OPTIMUM remains unchanged. Regardless of whether a size is specified or not, the rollback segment cannot shrink to less than two extents.

The SHRINK option is an attempt to shrink the size of the rollback segment; the success and amount of shrinkage depends on the following:

         o available free space in the rollback segment

	 o 	how active transactions are holding space in the rollback
                segment

Example III

This statement attempts to resize a rollback segment to an optimum size of one hundred megabytes:

ALTER ROLLBACK SEGMENT rsone

        SHRINK TO 100 M brian.maclean_at_teldta.com Received on Wed Apr 24 1996 - 00:00:00 CEST