Oracle rollback segment concepts, usage and planning and problem solving _oracle

Source: Internet
Author: User
Tags one table rollback oracle database
The Oracle tutorial being looked at is the concept, usage, and planning of the Oracle rollback segment and resolution of the problem.

Rollback segment management is always a difficult problem in Oracle database management, this paper introduces the concept, usage, planning and problem solving of Oracle rollback segment through an example.

Rollback Segment Overview

The rollback segment is used to hold the value before the data modification, including the position and value before the data was modified. The head of the rollback segment contains information about the rollback segment transaction that is in use. A transaction can only use a rollback segment to hold its rollback information, and a rollback segment may hold multiple transaction rollback information.

The role of the rollback segment

Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the front image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle will use the data before the rollback segment to restore the modified data to its 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, and Oracle will use the rollback to recover the uncommitted data the next time the database is opened.

Read consistency: When a session is modifying data, other sessions will not see the uncommitted changes to the session. Also, when a statement is executing, the statement does not see uncommitted modifications (statement-level read consistency) that have been executed since the statement started. When Oracle executes a SELECT statement, Oracle follows the current system change NUMBER-SCN to ensure that any uncommitted changes prior to the current SCN are not processed by the statement. As you can imagine: When a long-running query is executing, if another session changes a block of data to be queried by the query, Oracle constructs a read-consistent view with the data-fore image of the rollback segment.

Read consistency at transaction level

Oracle generally provides read consistency at the SQL statement level (SQL STATEMENT levels), and you can use the following statement to implement transactional-level read consistency.


All two of these statements provide read consistency after the transaction starts. It should be noted that using the second statement will have an impact on the concurrency and performance of the database.


Type of rollback segment

System rollback segment: When the database is created, a system rollback segment is automatically created, which is used only to hold the front image of objects in the system tablespace.

Non-system rollback segment: A database that has more than one table space should have at least one non-system rollback segment for storing data-front images of objects in a system-table space. The non-system rollback segment is divided into a private rollback segment and a public rollback segment, and the private rollback segment should be listed in the rollback segments parameter of the parameter file so that the routine is automatically online when it is started. Public rollback segments typically appear in OPS (Oracle parallel servers) and are automatically online when the routine is started.

defered rollback segment: The rollback segment is automatically created by the system when the tablespace is offline (OFFLINE) and automatically deleted by the system when the tablespace is online again, which is used to store rollback information generated when the tablespace is offline.

Use of rollback segment

Assign a rollback segment: When the transaction begins, Oracle assigns a rollback segment to the transaction and assigns the rollback segment with the least transaction to the transaction. A transaction can request a specified rollback segment with the following statement:

Transactions use the area of the rollback segment (extents) in a sequential, circular manner, and move to the next area when the current area is full. Several transactions can be written in the same area of the rollback segment, but the block of each rollback segment can only contain information for one transaction.

For example (two transactions use the same rollback segment, and the rollback segment has four extents):

1. Transactions are in progress, and they are using the third area of the rollback segment;

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

3, when the third area full, the transaction will be written to the fourth area, when the transaction begins to write to a new area, called Flip (WRAP);

4. When the fourth area is full, if the first zone is idle or inactive (all transactions using the zone are completed without active transactions), the transaction will then use the first zone.

Expansion of the rollback segment (EXTEND)

When all the blocks of the current rollback segment are exhausted and the transaction requires more rollback space, the pointer to the rollback segment moves to the next area. When the last area is run out, the pointer moves to the front of the first area. The rollback segment pointer moves to the next area if the next zone has no active transactions, and the pointer cannot cross the zone. When the next zone is in use, the transaction assigns a new zone to the rollback segment, which is called the extension of the rollback segment. The rollback segment extends to the number of the rollback segment area until the parameter maxextents of the rollback segment reaches the value.


Recovery and optimal parameters for rollback segments

The optimal parameter indicates where the rollback segment is shrunk to when it is idle, indicating that the optimal parameter of the rollback segment can reduce the waste of the rollback segment space.
Create a rollback segment



The rollback segment can be specified private or public when it is created and cannot be modified once it is created.

Minextents must be greater than or equal to 2

The pctincrease must be 0.

Optimal if you want to specify, you must be greater than or equal to the initial size of the rollback segment (specified by minextents)


Under normal circumstances, Initial=next

Set the optimal parameters to save space use

Do not set maxextents to unlimited

The rollback segment should be created within a specific rollback segment tablespace



Make the rollback segment online

When the rollback segment is created, the rollback segment is offline and cannot be used by the database, and the rollback segment must be online in order for the rollback segment to be exploited by the transaction. You can use the following command to make the rollback segment online:


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

Modifying storage parameters for a rollback segment

You can use the Alter ROLLBACK segment command to modify the storage parameters (including optimal,maxextents) of the rollback segment.





Reclaim space for a rollback segment

If the optimal parameter of the rollback segment is specified, Oracle automatically reclaims the roll segment to the optimal specified location. The user can also manually recycle the space of the rollback segment.



If the value of to integer is not indicated, Oracle will attempt to reclaim the location of the optimal.


To get the rollback segment offline

The rollback segment will be offline for the following two purposes:

1. Prevent new transactions from using the rollback segment;

2. The rollback segment must be deleted.




If a transaction is using the rollback segment, the status of the rollback segment will be pending OFFLINE after the command is run. After the transaction ends, the status is changed to offline, and the status of the segment can be rolled back by V$rollstat query.


Delete a rollback segment

You can delete a rollback segment when it is no longer needed or to be rebuilt to change the Initial,next or minextents parameters. To delete a rollback segment, do not allow the rollback segment to be offline.



Querying for information about rollback segments

Data dictionary used: Dba_rollback_segs

Information you can query: the identity of the rollback segment (segment_id), the name (segment_name), the table space (tablespace_name), type (OWNER), status (status). &nbs

[1] [2] Next page

The Oracle tutorial being looked at is the concept, usage, and planning of the Oracle rollback segment and resolution of the problem. P


Statistics for rolling back segments

Data dictionary: V$rollname,v$rollstat



The current active transaction of the rollback segment

Data dictionary: v$session,v$transaction


2 rows selected.

Planning for the number of rollback segments

For OLTP systems, there is a large number of small transactions, generally recommended:

A large number of small rollback segments, one rollback segment per four transaction, and no more than 10 transactions per rollback segment.

For batch processing, general recommendations:

Fewer large rollback segments, one rollback segment per transaction.

Problems and solutions of rollback segment

Problem one: The transaction requirements of the rollback segment space is not enough, the performance of the table space with full (ORA-01560 error), rollback segment extension reached the value of the parameter maxextents (ORA-01628).

Workaround: Add a file to the rollback segment tablespace or make the existing file larger, and increase the value of the maxextents.

Problem Two: Read consistency error (ORA-01555 SNAPSHOT TOO old)

Workaround: Increase the value of the minextents, increase the size of the zone, and set a high optimal value. 4 5 6

Previous page

prev [1] [2]

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.