Create Database   «Prev  Next»

Lesson 12 Creating additional rollback segments
Objective Create user rollback segments for the COIN database.

Creating Additional Rollback Segments in Oracle

Oracle 11g and 19c have evolved in terms of how they handle rollback and undo management, and there are differences compared to older versions of Oracle. In Oracle 11g and 19c, Oracle uses Automatic Undo Management (AUM)[1] by default, which means rollback segments are managed automatically using an UNDO tablespace instead of manually managing rollback segments as was done in earlier versions.
Here are key points:
  1. Automatic Undo Management (AUM):
    • Starting with Oracle 9i, Automatic Undo Management is the default mode of managing undo data, which eliminates the need to manually create rollback segments.
    • Oracle uses an UNDO tablespace to automatically manage undo records. You do not need to create or manage rollback segments manually anymore.
  2. SYSTEM Tablespace:
    • In older versions of Oracle (before AUM), rollback segments would sometimes be placed in the SYSTEM tablespace, but this is no longer required or recommended. With AUM, Oracle will allocate undo in the designated UNDO tablespace.
    • The use of the SYSTEM tablespace for rollback segments is not advised in modern Oracle releases, and it is largely deprecated.
  3. Changes in Oracle 11g and 19c:
    • In both Oracle 11g and Oracle 19c, you are not required to create additional rollback segments manually. Oracle automatically handles undo data management through the UNDO tablespace.
    • The UNDO_RETENTION parameter can be configured to control how long undo data is kept, but the overall process is automated.

Therefore, Oracle 11g and 19c have made managing rollback segments much easier with the introduction and continued support for Automatic Undo Management, and you don't have to worry about placing rollback segments outside the SYSTEM tablespace as was required in earlier versions.

Oracle Cloud DBA

Legacy RDBMS Oracle 8

An optional clause that only has meaning when you are running Parallel Server 
CREATE [PUBLIC] ROLLBACK SEGMENT segment_name
[TABLESPACE tablespace_name]
[STORAGE (INITIAL integer [M|K],
    NEXT integer [M|K],
    MINEXTENTS integer,
    MAXEXTENTS integer,
    OPTIMAL integer)];
  1. [PUBLIC]: An optional clause that only has meaning when you are running Parallel Server
  2. segment_name: The name of the rollback segment
  3. tablespace_name: The name of the tablespace where you want to store the rollback segment
  4. INITIAL integer: Specifies the size of initial extent to be allocated for the rollback segment
  5. NEXT integer: Specifies the size of any subsequent extents that are allocated to the rollback segment
  6. MINEXTENTS: Specifies the number of extents to initially allocate when the rollback segment is created. This must be at least two. Rollback segments must always have at least two extents.
  7. MAXEXTENTS: Specifies an upper limit on the number of extents that a rollback segment may have
  8. OPTIMAL: Tells Oracle8 the number of extents that you would prefer the rollback segment to have

The TABLESPACE and STORAGE clauses may be in any order, and all of the STORAGE parameters are optional. You need only specify the ones for which you do not want to use default values. You should always specify a tablespace though. Otherwise, the system tablespace is used by default. Placing a rollback segment in the system tablespace is considered bad form, and may have a negative impact on performance because of the extra I/O being added to an already heavily used tablespace.

Create Rollback Segments - Exercise

Click the Exercise link below to practice what you have learned. In this exercise, you will write some CREATE ROLLBACK SEGMENT commands for your COIN database.
Create Rollback Segments - Exercise

[1] Automatic Undo Management : Automatic Undo Management in Oracle 11g and 19c simplifies undo space management by eliminating the need for manual rollback segment management. It uses undo tablespaces to store undo information, which is crucial for transaction rollback, read consistency, and database recovery. Oracle automatically tunes the undo retention period based on factors like undo tablespace size and system activity, ensuring efficient use of resources.

SEMrush Software