Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

ALTER ROLLBACK SEGMENT

Purpose

Use the ALTER ROLLBACK SEGMENT statement to bring a rollback segment online or offline, change its storage characteristics, or shrink it to an optimal or specified size.

The information in this section assumes that your database is running in rollback undo mode (the UNDO_MANAGEMENT initialization parameter is set to MANUAL or not set at all).

If your database is running in Automatic Undo Management mode (the UNDO_MANAGEMENT initialization parameter is set to AUTO), then user-created rollback segments are irrelevant. In this case, Oracle returns an error in response to any CREATE ROLLBACK SEGMENT or ALTER ROLLBACK SEGMENT statement. To suppress these errors, set the UNDO_SUPPRESS_ERRORS parameter to TRUE.

See Also:

Prerequisites

You must have ALTER ROLLBACK SEGMENT system privilege.

Syntax

alter_rollback_segment::=

Text description of statements_129.gif follows
Text description of alter_rollback_segment


(storage_clause)

Semantics

rollback_segment

Specify the name of an existing rollback segment.

ONLINE

Specify ONLINE to bring 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.

See Also:

"Bringing a Rollback Segment Online: Example"

OFFLINE

Specify OFFLINE to take the rollback segment offline.

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 on Taking Rollback Segments Offline

You cannot take the SYSTEM rollback segment offline.

See Also:

Oracle9i Database Administrator's Guide for more information on making rollback segments available and unavailable

storage_clause

Use the storage_clause to change the rollback segment's storage characteristics.

Restriction on Rollback Segment Storage

You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment.

See Also:

storage_clause for syntax and additional information "Changing Rollback Segment Storage: Example"

SHRINK Clause

Specify SHRINK if you want Oracle to attempt 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:

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 on Shrinking Rollback Segments

In a Real Application Clusters environment, you can shrink only rollback segments that are online to your instance.

See Also:

"Resizing a Rollback Segment: Example"

Examples

Bringing a Rollback Segment Online: Example

This statement brings the rollback segment rbs_one online:

ALTER ROLLBACK SEGMENT rbs_one ONLINE; 

Changing Rollback Segment Storage: Example

This statement changes the STORAGE parameters for rbs_one:

ALTER ROLLBACK SEGMENT rbs_one 
   STORAGE (NEXT 1000 MAXEXTENTS 20); 
Resizing a Rollback Segment: Example

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

ALTER ROLLBACK SEGMENT rbs_one 
   SHRINK TO 100 M;