Conceptual usage planning and problem solving of Oracle rollback segment

Source: Internet
Author: User
Tags one table rollback oracle database

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.

SET TRANSACTION READ ONLY;

Or:

SET TANNSACTION SERIALIZABLE;

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:

SET TRANSTRACTION USE ROLLBACK SEGMENT rollback_segment
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: info-contact@alibabacloud.com 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.