Shulou(Shulou.com)05/31 Report--

This article is about how to solve ORA-01555 errors in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

ORA-01555: what is an ORA-01555 error when the snapshot is too old?

ORA-01555 is a common error in the running of Oracle database. Here is Oracle's classic description of ORA-01555 errors:

ORA-01555: snapshot too old (rollback segment too small)

Cause: rollback records needed by a reader for consistent read are

Overwritten by other writers

To put it simply, the fallback segment data for consistent reading is overwritten by other writing processes. The following is a typical example:

Point in time 1: session 1 starts the query operation on table A.

Point in time 2: session 2 modifies record X of table A.

Time point 3: when the query statement of session 1 queries record X, it is found through the SCN number that record X has been modified, and the time of modification (time point 2) is later than time point 1. In this way, Oracle will do consistent reading by saving the data before X modification (Before Image) in UNDO.

Time point 4: session 2 modifies the record Y of table An and performs a commit operation. In this way, the slot data of the transaction can be overwritten by Oracle.

Time point 5: session 2 modifies the record Z of table An and performs a commit operation. At this point, due to insufficient space in the UNDO tablespace, the data before the modification of record Y is overwritten by Oracle.

Time point 6: when record Y is queried by the query statement of session 1, it is found through the SCN number that record Y has been modified, and the modification time (time point 6) is later than time point 1. Therefore, Oracle will do consistent reading by saving the data before Y modification (Before Image) in UNDO. But at this time, the data before the record Y modification has been overwritten at point 5 in time. Therefore, the system will report an ORA-01555 error!

The causes and solutions of ORA-01555 errors are complex, and Oracle has written many articles about this error handling. In this book, on the one hand, we mainly aim at the automatic UNDO management technology (Automatic UNDO Management) after 9i, on the other hand, we only deal with the ORA-01555 error handling of ordinary tables, but do not pay attention to the ORA-01555 error handling of special objects such as LOB.

How do I get information about ORA-01555 errors?

First of all, the relevant information is obtained from the application session window and alert.log respectively.

For example, the application session window displays an error message:

ORA-01555: snapshot too old: rollback segment number 9 with name "_ SYSSMU1 $" too small

The Alert.log shows:

ORA-01555 caused by SQL statement below (Query Duration=9999 sec, SCN:0x000.008a7c2d)

Secondly, the QUERY DURATION is determined by alert.log. In the above example, 9999 seconds.

Third, determine the undo segment name from the application session information. For example: _ SYSSMU1 $.

Finally, determine the UNDO_ recovery value of the UNDO tablespace.

SQL > show parameter undo_retention

How to resolve ORA-01555 errors?

If QUERY DURATION > UNDO_RETENTION

At this point, Oracle cannot guarantee that the data will be read consistently when the committed transaction expires, that is, after the UNDO_RETENTION time has expired.

In this case, the most effective solution is to optimize the query statement and reduce the QUERY DURATION time of the statement. If you cannot optimize it, you can only refer to the QUERY DURATION time value to expand the UNDO_ duration value to ensure that the Oracle saves the UNDO information for a longer time.

Expanding the UNDO_ recovery value means that more UNDO tablespaces are required, and the calculation of UNDO tablespaces is also described below.

If QUERY DURATION

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.