How to troubleshoot Oracle ORA-01555 errors

First, understand under what circumstances Oracle will produce ORA-01555 errors:

Suppose there is a table1 table with 60 million rows of data, assuming that the full table Scan 1 times takes 2 hours, from the process:

1. At 1 o'clock, User a issued a select * from table1; no matter how the Table1 changes in the future, the correct result should be that user A will see the content at the 1 o'clock moment.

2. At 1:30, User B executes the update command, updating this record in line No. 40 million of the Table1 table, when user A's full table scan has not reached No. 40 million. This time, no doubt, this record in line No. 40 million is written to the rollback segment, assuming that the rollback segment is UNDOTS1, and if User A's full table scan reaches line No. 40 million, it should be correct to read the contents of 1 o'clock from the rollback segment UNDOTS1.

3. At this point, User B submits the operation he just made, but at this point, the system can still provide user A with the correct data, because the No. 40 million line of records is still in the rollback segment UNDOTS1, the system can according to the SCN to the rollback section to find the correct data, but to note that, A significant change has taken place in the No. 40 million line of records in UNDOTS1: It is possible that the data in the UNDOTS1 of the No. 40 million row in the rollback segment may be overwritten at any time because the record has been submitted!

4, because the user a long query time, and the business has been ongoing, UNDOTS1 rollback segment in the use of a number of different transaction, the rollback section of the extent loop to the No. 40 million row of data in the extent, because this record has been marked submitted, So this extent can be covered by other transaction!

5, at 1:45, user A's query finally to the No. 40 million line, and then there has been a 4th article said

Situation, need to rollback segment UNDOTS1 to find data, but has been covered off, so there is a ORA-01555 error.

Solution:

1. Expand the rollback segment, because the rollback segment is recycled, if the rollback segment is large enough, then those submitted data can be saved long enough to make those large transactions complete consistent reading. Undo table space is set large enough

2. Increase the undo_retention time, because the undo rollback segment is recycled, the data inside may be recycled at any time, and if the set undo_retention time is longer, no other transaction can overwrite the data within the time specified by retention.

3. The most important point is the optimization program-related query statements, reduce the consistency of query statements read, reduce the risk of reading the rollback segment data. All error messages are recorded in the database log alert_prod.log file, check the log file to find the appropriate SQL words, this statement to provide developers to analyze and optimize the program code. such as the decomposition of a large query into a few small queries, etc.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/