ORA-1555 reported inspite of high undo_retention and enough UNDO space.

刘泽宇

现象：
ORA-1555 is being reported inspite of high values set for the undo_retention and enough space in the UNDO tablespace.

o setting "_smu_debug_mode" = 33554432 didn't help.
o Tried many settings for UNDO_RETENTION, also didn't work.


原因：
This has been discussed within Bug:14700437 - SPORADIC ORA-1555 ON QUERIES where it was found that there are incorrect jumps in V$UNDOSTAT.TUNED_UNDORETENTION as there should be no increase of no more than 600 in any 10 minute period, in line with any increase in max query length.

处理方法：
At the time of writing this document Bug:14700437 is still under investigation. As a workaround disable tuned undoretention, using the below commands:

alter system set "_undo_autotune"=false scope=both

and set undo_retention manually to a value higher than the maximum query duration expected.