Application of Oracle Database rollback segments

Oracle DatabaseOfRollback segmentThe original data is retained when the database is corrected, so that the original data can be restored to the data before the correction through ROLLBACK later. In addition, the ROLLBACK segment can feel the read uniformity of all the processes in the database. this article focuses on Oracle Database rollback segments. Let's take a look at this part.

Types of rollback segments

Rollback segments can be divided into system rollback segments and non-system rollback segments. Non-system rollback segments can also be divided into PUBLIC rollback segments and PRIVATE rollback segments.

The SYSTEM rollback segment is used to process the CATALOG tasks that affect the SYSTEM (for example, most DDL operations). It is located in the SYSTEM tablespace. Because only the SYSTEM tablespace can be available at any time, do not place the SYSTEM rollback segments in other tablespaces.

: The SYSTEM rollback segment should be placed in the SYSTEM tablespace, and always stick to the ONLINE state.

PUBLIC rollback segments are available for all database instances unless explicitly set to OFFLINE.

PRIVATE rollback segments are PRIVATE to a database instance. To use PRIVATE rollback segments, an instance must be in its INITsid. the ROLLBACK_SEGMENTS of ORA indicate all private rollback segments to be used, or use alter rollback segment xxx online to use a specific rollback segment.

Advocate 1: In a single instance system, it is recommended that all rollback segments be set to PUBLIC.

Advocate 2: in Multi-instance systems (such as OPS), we recommend that you place the PRIVATE rollback segments of each instance on the local equipment with relatively fast access.

Quantity, size, and storage parameters of rollback segments

The calculation of the number and size of accurate rollback segments involves many aspects: the number of items that are used (OLTP, OLAP, and BATCH), the number of items that are simultaneously held, and the type of DML statements, the amount of data processed by each transaction.

Inch 2: The OLTP system should use small but large rollback segments, and the OLAP system/batch processing system should use a small number of large rollback segments.

Advocate 3: In OLTP/OLAP hybrid systems, one or several major rollback segments should be set, and usually set to OFFLINE, USE Time traversal to use set transaction use rollback segment xxx. these rollback segments should use the OPTIMAL parameter so that the SHRINK can be scaled to a smaller size at adverse times.

Advocate 4: It is difficult to calculate the accurate quantity and size, and the "too large or not small" percentage can be used.

Inch 3: The INITIAL/NEXT parameters of all rollback segments should be set to the same, except for the large rollback segments mentioned in advocate 3.

4: Do not set MAXEXTENTS of the rollback segment to UNLIMITED, or the tablespace of the rollback segment to AUTOEXTEND.

Otherwise, the full database will be out of control due to an abnormal transaction.

Maintenance and query of rollback segments

1. Create rollback segments

 
 

  
  
CREATE ROLLBACK SEGMENT RB01  
  
  
 
  
  
TABLESPACE RBS1  
  
  
 
  
  
STORAGE (  
  
  
 
  
  
INITIAL 100K  
  
  
 
  
  
NEXT 100K  
  
  
 
  
  
MINEXTENTS 20  
  
  
 
  
  
MAXEXTENTS 100  
  
  
 
  
  
OPTIMAL 2000K ); 
 
 

2. Change ONLINE/OFFLINE events

 
 

  
  
ALTER ROLLBACK SEGMENT RB01 ONLINE;  
  
  
 
  
  
ALTER ROLLBACK SEGMENT RB01 OFFLINE; 
 
 

3. Modify the OPTIMAL parameter

 
 

  
  
ALTER ROLLBACK SEGMENT RB01  
  
  
 
  
  
STORAGE ( MAXEXTENTS 200  
  
  
 
  
  
OPTIMAL 2048K ); 
 
 

4. Narrow the rollback segment

 
 

  
  
ALTER ROLLBACK SEGMENT RB01 SHRINK; 
 
 

(If there is an OPTIMAL parameter, it is reduced to the OPTIMAL value; if there is no OPTIMAL parameter, it is reduced to the size corresponding to MINEXTENTS)

 
 

  
  
ALTER ROLLBACK SEGMENT RB01 SHRINK TO 2048K; 
 
 

5. Modify the INITIAL/NEXT parameters.

Advocate 5: when the NEXT is corrected based on the percentage 3, the INITIAL should always be corrected at the same time.

The INITIAL parameter cannot be directly corrected. You can only DROP it first and then CREATE.

 
 

  
  
DROP ROLLBACK SEGMENT RB01;  
  
  
 
  
  
CREATE ROLLBACK SEGMENT RB01  
  
  
 
  
  
TABLESPACE RBS1  
  
  
 
  
  
STORAGE ( INITIAL 100K  
  
  
 
  
  
NEXT 100K  
  
  
 
  
  
MINEXTENTS 20  
  
  
 
  
  
MAXEXTENTS 121  
  
  
 
  
  
OPTIMAL 2000K ) 
 
 

6. Use specific rollback segments in transactions

 
 

  
  
SET TRANSACTION USE ROLLBACK SEGMENT RB_LARGE1; 
 
 

7. Common System data dictionaries related to rollback segments

 
 

  
  
DBA_ROLLBACK_SEGS (coherent table: DBA_SEGMENTS)
  
  
 
  
  
V $ ROLLNAME
  
  
 
  
  
V $ ROLLSTAT
  
  
 
  
  
V $ TRANSACTION (coherent table: V $ SESSION)
 
 

This article introduces Oracle Database rollback segments and hopes to bring you some benefits!