ALTER ROLLBACK SEGMENT

Syntax

storage_clause: See "storage_clause".

Purpose

To bring a rollback segment online or offline, to change its storage characteristics, or to shrink it to an optimal or specified size.

For information on creating a rollback segment, see "CREATE ROLLBACK SEGMENT".

Prerequisites

You must have ALTER ROLLBACK SEGMENT system privilege.

Keywords and Parameters

rollback_segment	specifies the name of an existing rollback segment.
ONLINE	brings the rollback segment online. When you create a rollback segment, it is initially offline and not available for transactions. This clause brings the rollback segment online, making it available for transactions by your instance. You can also bring a rollback segment online when you start your instance with the initialization parameter ROLLBACK_SEGMENTS.
OFFLINE	takes the rollback segment offline. If the rollback segment does not contain any information needed to roll back an active transaction, Oracle takes it offline immediately. If the rollback segment does contain information for active transactions, Oracle makes the rollback segment unavailable for future transactions and takes it offline after all the active transactions are committed or rolled back. Once the rollback segment is offline, it can be brought online by any instance.
	To see whether a rollback segment is online or offline, query the data dictionary view DBA_ROLLBACK_SEGS. Online rollback segments have a STATUS value of IN_USE. Offline rollback segments have a STATUS value of AVAILABLE. Restriction: You cannot take the SYSTEM rollback segment offline. See Also: Oracle8i Administrator's Guide for more information on making rollback segments available and unavailable.
storage_clause	changes the rollback segment's storage characteristics. See the "storage_clause" for syntax and additional information. Restriction: You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment.
SHRINK	attempts to shrink the rollback segment to an optimal or specified size. The success and amount of shrinkage depend on the available free space in the rollback segment and how active transactions are holding space in the rollback segment. The value of integer is in bytes, unless you specify K or M for kilobytes or megabytes. If you do not specify TO integer, then the size defaults to the OPTIMAL value of the storage_clause of the CREATE ROLLBACK SEGMENT statement that created the rollback segment. If OPTIMAL was not specified, then the size defaults to the MINEXTENTS value of the storage_clause of the CREATE ROLLBACK SEGMENT statement.
	Regardless of whether you specify TO integer: The value to which Oracle shrinks the rollback segment is valid for the execution of the statement. Thereafter, the size reverts to the OPTIMAL value of the CREATE ROLLBACK SEGMENT statement. The rollback segment cannot shrink to less than two extents. To determine the actual size of a rollback segment after attempting to shrink it, query the BYTES, BLOCKS, and EXTENTS columns of the DBA_SEGMENTS view. Restriction: For a parallel server, you can shrink only rollback segments that are online to your instance.

Examples

This statement brings the rollback segment RSONE online:

ALTER ROLLBACK SEGMENT rsone ONLINE; 

This statement changes the STORAGE parameters for RSONE:

ALTER ROLLBACK SEGMENT rsone 
   STORAGE (NEXT 1000 MAXEXTENTS 20); 

This statement attempts to resize a rollback segment to 100 megabytes:

ALTER ROLLBACK SEGMENT rsone 
   SHRINK TO 100 M;