Common Errors and Solutions of rollback segments

(1). insufficient space for rollback segments
ORA-01562-failed to extend rollback segment number string
The reason for insufficient rollback segment space is generally as follows:
A. The remaining free space in the tablespace where the rollback segment is located is too small to allocate the next extent.
B. The number of rollback segment extensions has reached the maxextents limit.

Solution:
A. Expand the tablespace of the rollback segment
B. Set large maxextents parameters.
C. Set the optimal parameter for the rollback segment
D. Use the large extent parameter to recreate the rollback segment.
C. Change the DML statement that causes ORA-1562 errors to be executed in segments:
For example, the statement is:
Delete from chenfeng where condition;
Use the following statement instead:
Begin
Loop
Delete from chenfeng
Where condition
And rownum: <10000;
Exit when SQL % notfound;
Commit;
End loop;
End;

(2). ORA-01552 cannot use system rollback segment for non-system tablespace 'string'
Cause: there is no available non-system rollback segment. It is divided into the following situations:
A. No other rollback segments are created except the system rollback segments.
B. Only private rollback segments are created, but these rollback segments are not listed in rollback_segments of initsid. ora.
C. Create public rollback segments, but all these rollback segments are in the offline status.
Solution: Solve the problem accordingly.

(3). ora_01555 snapshot too old: rollback segment number string with name "string" too small
The causes can be divided into the following situations:
A. Too few rollback segments/too small
If the database contains too many transactions to modify and commit data, the space used by the committed transactions will be reused, therefore, the data requested by a long-lasting query is no longer in the rollback segment.
Solution: create more rollback segments and set a large extent and a large minextents

B. the rollback segment is damaged.
The transaction cannot put the pre-modified content (read-consistent snapshot) into the rollback segment because the rollback segment is broken, which also produces a ORA-01555 error.
Solution: Remove and recreate the damaged rollback segment offline.

C. Fetch processing SS commit
When a process opens a cursor and executes fetch, update, commit cyclically, if the updated table and fetch are the same, a ORA-01555 error may occur.

Solution:

A. Use a large rollback segment

B. Reduce the submission frequency (see "how to avoid repeated calls to a procedure" in this forum, replies from anonymous friends)
The above two methods can only reduce the possibility of this error and cannot be completely avoided. If you want to avoid it completely, you must start with the execution method, you can use the following two methods:

C. Create a temporary table, store the query columns (such as primary keys and related condition columns) of the table to be updated, and update the original table from the temporary table fetch.

D. Capture ORA-01555 errors, close and re-open cursor, continue to execute the loop.

D. Other reasons:
* Delayed logging block cleanout is a mechanism used by Oracle to improve Write Performance: When an insert/update/delete operation occurs, Oracle writes the original content to the rollback segment, update the header of each data block so that it points to the corresponding rollback segment. When this operation is committed, Oracle does not re-access all data blocks to confirm all the modifications, instead, update the transaction slot in the rollback segment header to indicate that the transaction has been committed, this allows the write operation to end quickly, thus improving the performance. Any next operation that accesses the data modified by this operation will make the previous write operation take effect and access the new value. although delayed logging block cleanout improves performance, it may lead to ORA-01555. in this case, it is helpful to scan the entire table before open/fetch (to ensure that all modifications are confirmed.

There are two types of delayed cleanout:

1. Because the corresponding rollback segment information has not been reused, the process accessing these data blocks that are not cleanout can determine the SCN at commit.

2. because it takes too long to obtain the exact SCN, this data block will be marked with a 'best guess' SCN or 'Upper bound commit 'number.

If the cleanout transaction has been running for too long, oracle cannot obtain the 'upper bound commit 'number based on the rollback segment information (too many times the rollback segment was reused during the transaction execution), and a well-known ORA-1555 would occur.

* Inappropriate optimal parameter: If the optimal parameter is too small, the rollback segment will be quickly shrink, causing the previous content to be lost during subsequent read operations. carefully design the optimal parameter and do not allow extend/shrink with too frequent rollback segments to help solve the problem.

* Db block buffer is too small: if the previous content of the requested block is in the buffer, you do not need to access the rollback segment. if the buffer is too small, the possibility of the previous version of content in the cache becomes smaller, so that you must frequently access the rollback segment to obtain the previous content, which will greatly increase the possibility of ORA-01555.

Possible situations that produce ORA-1555:
(1) A long-running query with DML processing for the chunks required for the query
When a long query starts to be executed, the data block required for the query is modified and submitted. The data block must be read in the same way, but because the DML transaction has been submitted, therefore, the rollback slot of the original image can be used by another transaction. This query transaction takes a very long time. During this period, it is very likely that the slot is used by another transaction and the original value is overwritten. Therefore, when the query is executed to the block, the value of the block's snapshot SCN cannot be found, ORA-1555 error reported.
Solution:
Business control: It is prohibited to perform long-time query and update processing on the same table at the same time. Separate execution is required.
Increase the number of rollback segments
Increase the size of the rollback segment and remember that the error that produces the ORA-1555 may be caused by the smallest rollback segment, so the size of each rollback segment should be consistent.
Do not use the optimal option
Delay the commit of DML statements
Shorten the query time and modify query statements, such as parallel queries.
Create a read-only snapshot for the table to be queried, so that modification to the table record will not affect the query, but the table cannot be too large.
(2). Cross submission
Create a loop using a cursor to read the table data cyclically, but modify and submit the table in the loop. If multiple modifications are made and submitted, overwrite the image of a data block before the rollback segment and report a ORA-1555 error when the loop is about to fetch the value of the data block. This is a frequent ORA-1555 error operation.
Solution:
Modify the program to avoid this situation.
Do not submit data for loop reading.
In the query statement, add the "Order by 1" statement, so that the result of order by is retained in the temporary segment, without the need for consistent reading.
(3). Delay block clearing
When a DML transaction occurs and is committed, Oracle makes a quick commit in the rollback segment to mark that the transaction has been committed, however, the modified data blocks in data buffer are not marked (Oracle only clears the modified data blocks of Data buffer10 % at a time) these uncleared data blocks that have been submitted must be cleared before the next transaction access. This is the deletion of delayed data blocks. In this process there may be a ORA-1555 error, but it is generally very rare, because the error needs to meet the following conditions:
① Data blocks that have been modified and submitted but not cleared
② These blocks are not used by other transactions before being used by the next transaction to be faulty.
③ During the query, a large amount of other DML processing occurs in the system. These DML processing does not involve these blocks.
④ Because of these large amounts of DML transactions, frequent submission is generated, resulting in the transaction table being used for multiple wrap times, and the transaction entries that are not cleared at first are also recycled, the original update commit SCN does not exist.
⑤ The minimum SCN of the rollback segment has exceeded the query SCN.
⑥ When the query runs on the block, it is found that the update commit SCN of the block does not exist, and the minimum SCN of the rollback segment is larger than the query SCN, upper Bound SCN cannot be estimated, so it cannot be determined whether the query can use this block, resulting in a ORA-1555 error.
Solution:
In general, this situation is very difficult to solve. If you do want to solve it, you can perform a full table scan after the DML operation to clear the last uncleared block.
You can set delayed_logging_block_cleanouts to true (default)
(4). the rollback segment has bad blocks.

To sum up, we can see from the above description that the most fundamental point to prevent the problem of ORA-1555 is to ensure that the transaction information of the commit in the rollback segment is not covered. In 9i, Oracle provides a more effective method to ensure this. The undo_retention parameter is used to ensure that the commit transactions are not overwritten for a long time. For more information, see the automatic rollback segment Management (SMU) of Oracle9i ).