[Oracle] An analysis of the ORA-01555 problem of maddening _oracle

Therefore, in the daily maintenance of the database, if only two times to encounter ORA-01555 errors, generally ignored, but if you often encounter this error, you need to make some adjustments to avoid the recurrence of this 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.