ORACLE rollback segment Management

Source: Internet
Author: User

Rollback segment management has always been a problem in ORACLE Database Management. This article introduces the concept, usage, planning, and problem solving of ORACLE rollback segments through examples.

Rollback segment Overview

Rollback segments are used to store values before data modification (including locations and values before data modification ). The header of the rollback segment contains the information about the transaction in use. A transaction can use only one rollback segment to store its rollback information, while a rollback segment can store the rollback information of multiple transactions.

Roles of rollback segments

Transaction ROLLBACK: when the transaction modifies the data in the table, the value before the data modification (that is, the previous image) will be stored in the ROLLBACK segment. When the user rolls back the transaction (ROLLBACK) ORACLE will use the data pre-image in the rollback segment to restore the modified data to the original value.

Transaction recovery: when the transaction is being processed, the routine fails, and the rollback segment information is saved in the redo log file, ORACLE will use rollback to restore uncommitted data the next time it opens the database.

Read consistency: When a session is modifying data, other sessions will not be able to see the modifications not submitted by the session. In addition, when a statement is being executed, the statement cannot see uncommitted modifications (statement-level read consistency) after the statement is executed ). When ORACLE executes a SELECT statement, ORACLE ensures that any uncommitted changes earlier than the current SCN are not handled by the current system change number (SYSTEMCHANGENUMBER-SCN. It can be imagined that when a long query is being executed, if other sessions change the data block to be queried for this query, ORACLE uses the data pre-image of the rollback segment to construct a read consistency view.

Transaction-level read consistency

ORACLE generally provides SQL statement-level (SQLSTATEMENTLEVEL) read consistency. You can use the following statements to achieve transaction-level read consistency.




The preceding two statements provide read consistency after the transaction starts. Note that using the second statement will affect the database concurrency and performance.

Types of rollback segments

System rollback segment: After a database is created, a system rollback segment is automatically created. This rollback segment is only used to store the pre-image of objects in the system tablespace.

Non-system rollback segments: databases with multiple tablespaces should have at least one non-system rollback segment, which is used to store the pre-image of objects in non-system tablespaces. Non-system rollback segments are classified into private and public rollback segments. Private rollback segments should be listed in the ROLLBACKSEGMENTS parameter of the parameter file, so that the routine is automatically ONLINE at startup ). The public rollback segment is generally found in OPS (ORACLE Parallel Server) and will be automatically online when the routine is started.

DEFERED rollback segment: This rollback segment is automatically created by the system when the tablespace is OFFLINE (OFFLINE). When the tablespace is ONLINE again, it is automatically deleted by the system, used to store rollback information generated when the tablespace is offline.

Use of rollback segments

Allocate rollback segments: when the transaction starts, ORACLE allocates rollback segments for the transaction and assigns rollback segments with the least number of transactions to the transaction. A transaction can use the following statement to apply for a specified rollback segment:


The transaction uses the EXTENTS of the rollback segment in order and cyclically. When the front zone is full, the transaction is moved to the next zone. Several transactions can be written in the same partition of the rollback segment, but each block of the rollback segment can only contain information of one transaction.

For example, if two transactions use the same rollback segment, the rollback segment has four partitions ):

1. transactions are in progress and they are using the third partition of the rollback segment;

2. When two transactions generate more rollback information, they will continue to use the third partition;

3. When the third partition is full, the transaction will be written to the fourth partition. When the transaction starts to write to a new partition, it is called a flip (WRAP );

4. When the fourth zone is full, if the first zone is idle or inactive (all transactions in the zone are used to complete but no active transactions, the transaction uses the first partition.

Expansion of rollback segments (EXTEND)

When all the blocks in the current rollback segment are used up and the transaction requires more rollback space, the rollback segment pointer will be moved to the next partition. When the last partition is used up, the pointer is moved to the front of the first partition. The precondition for moving the rollback segment pointer to the next zone is that there are no active transactions in the next zone, and the pointer cannot be cross-zone. When the next partition is in use, the transaction will allocate a new partition for the rollback segment, which is called an extension of the rollback segment. The rollback segment is extended until the number of rollback segments reaches the value of the parameter MAXEXTENTS of the rollback segment.

Rollback segment recycling and OPTIMAL parameters

The OPTIMAL parameter specifies the position where the rollback segment is reduced when it is idle, and the OPTIMAL parameter of the rollback segment can reduce the waste of the rollback segment space.
Create rollback segments


Create [public] rollback segment rollback_segment
[Storage ([initial integer [k | M] [next integer [k | M]
[Minextents integer]
[Maxtents {INTEGER | unlimited}]
[Optimal {INTEGER [k | M] | null}])]


  • You can specify PRIVATE or PUBLIC when creating a rollback segment.
  • Creation cannot be modified.
  • MINEXTENTS must be greater than or equal to 2
  • PCTINCREASE must be 0
  • OPTIMAL to be specified, it must be greater than or equal to the initial size of the rollback segment (specified by MINEXTENTS)
  • Suggestion:

    • In general, initial = next
    • Set the optimal parameter to save space
    • Do not set maxextents to unlimited
    • Rollback segments should be created in a specific rollback segment tablespace.


    Create rollback segment rbs01
    Tablespace RBS
    Storage (initial 100 K next 100 k minextents 10
    Maxextents 500 optimal 1000 K );

    Online rollback segments

    After a rollback segment is created, the rollback segment is offline and cannot be used by the database. To enable the rollback segment to be used by transactions, the rollback segment must be online. You can use the following command to make the rollback segment online:

    Alter rollback segment rollback_segment online;


    Alter rollback segment rbs01 online;

    To make the rollback segment automatically online when the database starts, you can list the name of the rollback segment in the database parameter file. For example, add the following line to the parameter file:

    Rollback_segment = (rbs01, rbs02)

    Modify the storage parameters of a rollback segment

    You can use the alter rollback segment command to modify the storage parameters of ROLLBACK segments (including OPTIMAL and MAXEXTENTS ).


    Alter rollback segment rollback_segment
    [Storage ([next integer [k | M]
    [Minextents integer]
    [Maxextents {INTEGER | unlimited}]
    [Optimal {INTEGER [k | M] | null}])]


    Alter rollback segment rbs01 storage (maxextents 1000 );

    Reclaim the space of the rollback segment

    If the OPTIMAL parameter of the rollback segment is specified, ORACLE automatically recycles the rollback segment to the position specified by OPTIMAL. You can also manually reclaim the space of the rollback segment.


    Alter rollback segment rollback_segment SHRINK [TO integer [K | M];


    If the value TO integer is not specified, ORACLE tries TO recycle it TO the OPTIMAL position.


    Alter rollback segment rbs01 shrink to 2 M;

    Offline rollback segments

    To achieve the following two objectives, the rollback segment will be offline:

    1. prevent new transactions from using the rollback segment;

    2. the rollback segment must be deleted.


    Alter rollback segment rollback_segment offline;


    Alter rollback segment rbs01 offline;


    If a transaction is using this rollback segment, after running this command, the rollback segment status will be pending offline. After the transaction ends, the status changes to OFFLINE. You can use V $ ROLLSTAT to query the status of the rollback segment.

    Delete rollback segments

    You can delete a rollback segment when the INITIAL, NEXT, or MINEXTENTS parameters are no longer required or need to be rebuilt. You cannot delete a rollback segment offline.


    Drop rollback segment rollback_segment;


    Drop rollback segment rbs01;

    Query rollback segment information

    Data dictionary used: DBA_ROLLBACK_SEGS

    Information that can be queried: segment_id, segment_name, tablespace_name, owner, and status ).


    SQL> select segment_name, tablespace_name, owner, status from dba_rollback_segs;

    Rollback segment statistics

    Data Dictionary: V $ rollname, V $ rollstat


    SQL> select N. Name, S. extents, S. rssize, S. optsize, S. hwmsize, S. xacts, S. Status
    From v $ rollname N, V $ rollstat s
    Where n. USN = S. USN;

    The current active transaction of the rollback segment

    Data Dictionary: V $ session, V $ transaction


    SQL>SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublkFROM v$session s,v$transaction tWHERE s.saddr=t.ses_addr; USERNAMEXIDUSN   UBAFIL UBABLKUSED_UBLK ---------------  ----------- -----------  ----------- SYSTEM22  71 SCOTT 121631 2 rows selected. 

    Number of rollback segments

    For the OLTP system, a large number of small transactions are processed. It is generally recommended that: a large number of small rollback segments; one rollback segment for each four transactions; each rollback segment should not exceed 10 transactions.

    For batch processing, we generally recommend that you use a small large rollback segment and one rollback segment for each transaction.

    Rollback segment Problems and Solutions

    Problem 1: The rollback segment space required by the transaction is insufficient, as the tablespace is full (ORA-01560 error), and the rollback segment extension reaches the value of the maxextents parameter (ORA-01628 ).

    Solution: Add a file to the tablespace of the rollback segment or make the existing file larger. Increase the value of maxextents.

    Problem 2: read consistency error (ORA-01555 snapshot too old)

    Solution: Increase the minextents value, increase the partition size, and set a high optimal value.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.