CREATE ROLLBACK SEGMENT

Purpose

Use the CREATE ROLLBACK SEGMENT statement to create a rollback segment, which is an object that Oracle uses to store data necessary to reverse, or undo, changes made by transactions.

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.

Note: To use objects in a tablespace other than the SYSTEM tablespace: If you are running the database in rollback undo mode, at least one rollback segment (other than the SYSTEM rollback segment) must be online. If you are running the database in Automatic Undo Management mode, at least one UNDO tablespace must be online.

See Also: ALTER ROLLBACK SEGMENT for information on altering a rollback segment DROP ROLLBACK SEGMENT for information on removing a rollback segment Oracle9i Database Reference for information on the UNDO_MANAGEMENT and UNDO_SUPPRESS_ERRORS parameters

Prerequisites

To create a rollback segment, you must have CREATE ROLLBACK SEGMENT system privilege.

Syntax

create_rollback_segment::=

Text description of create_rollback_segment

storage_clause: See storage_clause.

Keyword and Parameters

PUBLIC

Specify PUBLIC to indicate that the rollback segment is public and is available to any instance. If you omit this clause, the rollback segment is private and is available only to the instance naming it in its initialization parameter ROLLBACK_SEGMENTS.

rollback_segment

Specify the name of the rollback segment to be created.

TABLESPACE

Use the TABLESPACE clause to identify the tablespace in which the rollback segment is created. If you omit this clause, Oracle creates the rollback segment in the SYSTEM tablespace.

Restriction: You cannot create a rollback segment in a tablespace that is system managed (that is, during creation you specified EXTENT MANAGEMENT LOCAL AUTOALLOCATE).

Notes: A tablespace can have multiple rollback segments. Generally, multiple rollback segments improve performance. The tablespace must be online for you to add a rollback segment to it. When you create a rollback segment, it is initially offline. To make it available for transactions by your Oracle instance, bring it online using the ALTER ROLLBACK SEGMENT statement. To bring it online automatically whenever you start up the database, add the segment's name to the value of the ROLLBACK_SEGMENTS initialization parameter.

See Also: CREATE TABLESPACE Oracle9i Database Administrator's Guide for more information on creating rollback segments and making them available

storage_clause

The storage_clause lets you specify the characteristics for the rollback segment.

Notes: The OPTIMAL parameter of the storage_clause is of particular interest, because it applies only to rollback segments. You cannot specify the PCTINCREASE parameter of the storage_clause with CREATE ROLLBACK SEGMENT.

See Also: storage_clause

Examples

CREATE ROLLBACK SEGMENT Example

The following statement creates a rollback segment with default storage values in the system tablespace:

CREATE ROLLBACK SEGMENT rbs_1

TABLESPACE system; 

The above statement is equivalent to the following:

CREATE ROLLBACK SEGMENT rbs_2
   TABLESPACE system
   STORAGE
   ( INITIAL 10K
     NEXT 10K
     MAXEXTENTS UNLIMITED);