The causes of ORA-01555 errors and the solutions

The first point to note is: ORA-01555 error is a "safe" error, it will not cause data loss or corruption, but will allow the query to receive the error can not continue, therefore, in the day-to-day maintenance of the database, if only two times to encounter ORA-01555 errors, generally ignored, However, if you frequently encounter this error, make some adjustments to avoid the recurrence of the error.

1. Why are ORA-01555 errors generated?

This error is caused by the read consistency of the database (read consistency), and when the query needs to access the modified data, it accesses the previous mirror image of the data in undo, and if the previous mirror is overwritten, the query statement returns a ORA-01555 error.

2. What are the main causes of ORA-01555 errors?

1 Undo is too small

A more intuitive solution is for the DBA to tell the database how long it takes to apply the longest query, and set the undo_retention to this value, while increasing the amount of the Undo table space accordingly.

But to fundamentally solve the undo too small problem, but also from the application-side code tuning to reduce the running time of the query.

2) Delayed block cleanup

This is a rare situation, especially in an OLTP system, which may be encountered in an OLAP system, and the solution is to scan the related object with the Dbms_stats package after each large insert or update.

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