Concept, usage, planning, and problem solving of Oracle rollback segments

The ORACLE tutorial is about the concept, usage, planning, and problem solving of Oracle rollback segments.

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 processed by the current system change NUMBER-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 read consistency at the SQL statement level (SQL STATEMENT LEVEL). You can use the following statements to achieve read consistency at the transaction LEVEL.

Or:

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

[NextPage]

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 rollback segments 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.

[NextPage]

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

Syntax:

Note:

You can specify PRIVATE or PUBLIC when creating a rollback segment. Once created, it 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.

Example:

7

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:

Example:

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:

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 ).

Syntax:

Example:

7

[NextPage]

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.

Syntax:

Note:

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

Example:

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.

Syntax:

Example:

Note:

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.
7

[NextPage]

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.

Syntax:

Example:

Query rollback segment information

Data dictionary used: DBA_ROLLBACK_SEGS

Information that can be queried: SEGMENT_ID, SEGMENT_NAME, TABLESPACE_NAME, OWNER, and STATUS ). & Nbs

[1] [2] Next page

The ORACLE tutorial is about the concept, usage, planning, and problem solving of Oracle rollback segments. P;

Example:

Rollback segment statistics

Data Dictionary: V $ ROLLNAME, V $ ROLLSTAT

Example:

[NextPage]

The current active transaction of the rollback segment

Data Dictionary: V $ SESSION, V $ TRANSACTION

Example:

2 rows selected.

Number of rollback segments

For OLTP systems, there are a large number of small transaction processing. Generally, we recommend that you:

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, it is generally recommended that:

Large rollback segments; 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. 4 5 6

Previous Page

Previous Page [1] [2]