Oracle7 Server SQL Reference

ALTER ROLLBACK SEGMENT

Purpose

To alter a rollback segment in one of these ways:

by bringing it online

by taking it offline

by changing its storage characteristics

by shrinking it to an optimal or given size

Prerequisites

You must have ALTER ROLLBACK SEGMENT system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the rollback segment's creation label or you must satisfy one of these criteria:

If the rollback segment's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the rollback segment's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the rollback segment's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

rollback_segment specifies the name of an existing rollback segment.

ONLINE

brings the rollback segment online.

OFFLINE

takes the rollback segment offline.

STORAGE

changes the rollback segment's storage characteristics. See the STORAGE clause .

SHRINK

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

Usage Notes

When you create a rollback segment, it is initially offline. An offline rollback segment is not available for transactions.

The ONLINE option 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 .

The OFFLINE option takes the rollback segment offline. If the rollback segment does not contain information necessary to rollback any active transactions, Oracle7 takes it offline immediately. If the rollback segment does contain information for active transactions, Oracle7 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.

You cannot take the SYSTEM rollback segment offline.

You can tell whether a rollback segment is online or offline by querying the data dictionary view DBA_ROLLBACK_SEGS. Online rollback segments are indicated by a STATUS value of 'IN_USE'. Offline rollback segments are indicated by a STATUS value of 'AVAILABLE'.

For more information on making rollback segments available and unavailable, see the "Managing Rollback Segments" chapter of Oracle7 Server Administrator's Guide.

The STORAGE clause of the ALTER ROLLBACK SEGMENT command affects future space allocation in the rollback segment. You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment.

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.

You can query the DBA_ROLLBACK_SEGS tables to determine the actual size of a rollback segment after attempting to shrink a rollback segment.

For a parallel server, you can only shrink rollback segments that are online to your instance.

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

available free space in the rollback segment

how active transactions are holding space in the rollback segment

Example I

This statement brings the rollback segment RSONE online:

ALTER ROLLBACK SEGMENT rsone ONLINE

Example II

This statement changes the STORAGE parameters for RSONE:

ALTER ROLLBACK SEGMENT rsone 
	STORAGE (NEXT 1000 MAXEXTENTS 20)

Example III

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