Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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:
|
You must have ALTER
ROLLBACK
SEGMENT
system privilege.
alter_rollback_segment::=
Specify the name of an existing rollback segment.
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
.
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
.
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 |
Use the storage_clause
to change the rollback segment's storage characteristics.
You cannot change the values of the INITIAL
and MINEXTENTS
for an existing rollback segment.
See Also:
|
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
:
OPTIMAL
value of the CREATE
ROLLBACK
SEGMENT
statement.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.
In a Real Application Clusters environment, you can shrink only rollback segments that are online to your instance.
This statement brings the rollback segment rbs_one
online:
ALTER ROLLBACK SEGMENT rbs_one ONLINE;
This statement changes the STORAGE
parameters for rbs_one
:
ALTER ROLLBACK SEGMENT rbs_one
STORAGE (NEXT 1000 MAXEXTENTS 20);
This statement attempts to resize a rollback segment to 100 megabytes:
ALTER ROLLBACK SEGMENT rbs_one
SHRINK TO 100 M;