Rolling segment management has always been a problem for Oracle database management, and this article introduces the concept, usage and planning and problem solving of Oracle returns.
Rolling segment overview
Rollback segments are used to store values prior to data modification (including the positions and values before data modification). The head of the roll band contains information that is being used. A transaction can only use a rollover segment to store its rollove information, and a rollback segment can store the rollback information of multiple transactions.
Rotation
The transaction rollback: When the data is modified in the table, the value before the data is modified (ie, the front image) will be placed in the rollback segment. When the user rolls back the transaction (ROLLBACK), Oracle will use the rollback segment. Data-before image to restore the modified data to the original value. Transaction Recovery: When the transaction is being processed, the routine fails, the information of the rollover segment is saved in the redo log file, and Oracle will use rollback to restore uncommitted data when the database is turned on next time. Read consistency: When a session is modifying data, other sessions will not see the modifications of the session. Moreover, when a statement is executing, the statement will not see the uncommitted modification (statement class read consistency) from the statement starts. When Oracle executes the SELECT statement, Oracle ensures that any changes before the current SCN are not submitted in accordance with the current SCN, in accordance with the statement of the current SCN. I can imagine: When a long query is being executed, if other sessions change the query to query a data block to query, Oracle will construct a read consistency view using the data image of the rollback segment.
Transaction-level read consistency
Oracle typically provides a SQL statement (SQLStatementLevel) read consistency, you can use the following statement to implement transaction-level read consistency. SetTransactionReadOnly; or: settannsactionSerializable; The above two statements will provide read consistency after the transaction begins. It should be noted that the combination and performance of the database will have an impact on the use of the second statement.
Type of rollback
The system returns: When the database is created, a system rollback segment will be automatically created, which is only used to store the front image of the object in the system table space. Non-system rollback segments: The database with multiple tablespace should have at least one non-system returning segment that stores the data before the object in the system tablespace. Non-system returning segments are divided into private returns and public returns, and private returns should be listed in the RollbackSegments parameter of the parameter file so that it automatically enables online (Online) when the routine is started. The public returns typically appears in the OPS (Oracle Parallel Server), which will automatically online when the routine is started. DEFERED rollback segment: This rollback segment is automatically created by the system when the table space is offline (OFFLIN). When the table space is online (online), the system is automatically deleted, and the rollback information generated when the table space is deployed.
Use of the rollback segment
Assignback Square: When the transaction begins, Oracle will assign a rollback segment for the transaction and assign a returned segment with the minimum transaction to the transaction. The transaction can be applied for a specified rollback segment using the following statement: The SetTranstractionUserollbackSegmentrolLBack_SEGMENT transaction will use the rollover segment in order, and looped, when the current area is transferred back to the next area. Several transactions can be written in the same area of the rollback segment, but the block of each rollover can only contain a transaction information. For example (two transactions use the same return segment, there are four districts): 1, transactions are in progress, they are using the third zone of the rollback segment; 2, when two transactions are generated more The rollback information, they will continue to use the third zone; 3. When the third area is full, the transaction will write to the fourth area. When the transaction begins to write a new area, it is called flip (WRAP) 4, when the fourth area is full, if the first area is idle or non-active (all transactions with all transactions of the area, the transaction will then use the first zone. Roll to the expansion of the segment (Extend)
When all blocks of the current returning segment area do not need more roll spaces, the pointer to the next area will be moved to the next area. When the last area is used, the pointer will move to the front of the first zone. The premise of returning a roll-to-segment pointer is the next area is the next area without activity, and the pointer cannot be cross-zone. When the next area is being used, the transaction will assign a new zone to the rollback segment, which is called the expansion of the rollback segment. The rollback segment will extend the number of parameters maxExtents that reaches the number of parameters of the rollback segment.
Recycling and Optimal parameters of rollback segments
The Optimal parameter indicates the position of the retracted segment idle, indicating that the Optimal parameter of the rollback segment can reduce the waste of the spatial space.
Create a rolling segment
grammar:
CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment [TABLESPACE tablespace] [STORAGE ([INITIAL integer [K | M]] [NEXT integer [K | M]] [MINEXTENTS integer] [MAXTENTS {integer | UNLIMITED}] [OPTIMAL {integer [K | M] | NULL}])])])])])
Note:
Rollback segments You can specify private or public when you create a private or public, once you created it will not be modified. MINEXTENTS must be greater than or equal to 2 pctincrease must be 0 Optimal If you want to specify, it must be greater than or equal to the initial size of the rollback segment (specified by Mineltnts):
In general, INITIAL = next sets the Optimal parameter to save space Use Do not set MaxExtents to create a specific rollback segment table space interior example:
Create Rollback Segment RBS01 TABLESPACE RBSStorage (Initial 100K Next 100k Minelt1) 10 MaxExtents 500 Optimal 1000K);
Let go back to roll
When the segment is created, the rollback segment is offline, and cannot be used by the database. In order to make the roll band are utilized, the rollback must be returned online. You can use the following command to make the rollback group online:
Alter Rollback Segment Rollback_SEGMENT Online; Example:
Alter Rollback Segment RBS01 Online; To make the rollback segment automatically online when the database is started, you can list the name of the rollback segment in the parameter file of the database. For example, add the following lines in the parameter file: rollback_segment = (RBS01, RBS02)
Modify the storage parameters of the rollover segment
You can use the ALTER ROLLBACK segment command to modify the storage parameters of the rollback segment (including Optimal, MaxExtents). grammar:
Alter Rollback Segment Rollback_SEGMENT [Storage ([NEXTENTS INTEGER] [MINEXTENTS INTEGER] [MAXEXTENTS {Integer | Unlimited}] [Optimal {Integer [k | m] | NULL}]) Example:
Alter Rollback Segment RBS01 Storage (maxExtents 1000);
Recycling the space of the rollover segment
If the Optimal parameter of the rollover segment is specified, Oracle will automatically reclaim the position to the Optimal specified. The user can also manually recover the space of the rollback segment. Syntax: ALTER ROLLBACK Segment Rollback_SEGMENT SHRINK [To Integer [K | M]]; Description: If you do not specify the value of To Integer, Oracle will attempt to reclaim the location of Optimal. Example: ALTER ROLLBACK Segment RBS01 Shrink To 2M;
Reverse the rollback segment
In order to reach the following two purposes will be offline: 1. Block new transactions from using this rollback segment; 2. The rollback segment must be deleted. grammar:
Alter Rollback Segment Rollback_SEGMENT OFFLINE; Example:
ALTER ROLLBACK Segment RBS01 Offline; Description: If there is a transaction is using the rollback segment, run the command, the status of the rollback segment will be Pending Offline. After the event is over, the status will be changed to OFFLINE, and the status of the rollback segment can be queried by V $ ROLLSTAT.
Delete rollback segment
It can be deleted when the split is no longer needed or to be rebuilt to change the initial, next, or minexts parameter. To delete the rollback segment, do not allow the rollback segment to leave. grammar:
Drop rollback segment rollback_segment; example:
Drop rollback segment rbs01;
Query the information of the rollback segment
The data dictionary used: DBA_ROLLBACK_SEGS can query information: the flag of the segment (segment_id), name (segment_name), the table space (TableSpace_name), Type (Owner), Status. example:
SQL> SELECT Segment_name, TableSpace_name, Owner, Status from DBA_ROLLBACK_SEGS
Statistics of the rollback segment
Data Dictionary: V $ ROLLNAME, V $ ROLLSTAT:
SQL> SELECT N.NAME, S.EXTENTS, S.RSSIZE, S.OPTSIZE, S. HWMSIZE, S.XACTS, S.STATUSFROM V $ ROLLNAME N, V $ ROLLSTAT SWHERE N.USN = S.USN
The current activity of the rollback segment
Data Dictionary: V $ Session, V $ Transaction Example:
SQL> Select S.Username, T.Xidusn, T.UBAFIL, T.ubaBLK, T.USED_UBLKFROM V. SESSION S, V $ Transaction T
WHERE S.SADDR = T.SES_ADDR;
Usernamexidusn ubafil ubablkused_ublk
----------------------------------
SYSTEM22 71
Scott 121631
2 rows selected.
Quantity planning
For OLTP systems, there is a large number of small transaction processing, general recommendations: many small back to roll; every four transactions are returned; each returning segment should not exceed ten transactions. For batch processing, general recommendations: less big return segments; each transaction is a rollover segment.
Question and solution
Question 1: The spatial space required by the transaction is not enough, and it is manifested as a table space (ORA-01560 error), and the rollback segment extends to the value of the parameter maxExtents (ORA-01628). Workaround: Add files to the split segment table space or make the existing file becomes large; increase the value of MaxExtents. Question 2: Read consistency errors (ORA-01555 Snapshot Too OLD) Solution: Increase the value of MineXtents, increase the size of the zone, set a high Optimal value.
