ORA-00376: file 92 cannot be read at this time





How oracle manage the undo retention for UNDO TBS ?

Oracle automatically tunes (This feature was introduced in 10g) the retention period based on the longest running query on the system. The undo_retention will set minimal threshold but the actual TUNED_UNDORETENTION (can be obtained from V$UNDOSTAT) can be higher than the specified minimum retention period (i.e. than the UNDO_RETENTION value).

When the Undo tablespace is autoextended then the UNDO_RETENTION will represent the low threshold but the actual UNDO RETENTION can be higher than the specific UNDO_RETENTION parameter and you can view the actual retention period by query the TUNED_UNDORETENTION column in V$UNDOSTAT.

Oracle will try to honor the minimum threshold but there’s no guarantee that Oracle will be able to honor that (for example when the maximum size has reached and Oracle has to overwrite unexpired undo records).

If you’d like that Oracle will honor the low threshold of the UNDO_RETENTION parameter in any price, you can specify RETENTION GUARANTEE (in the CREATE UNDO TABLESPACE or ALTER TABLESPACE commands) and then Oracle never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

The undo_retention parameter specifies the minimum undo period that Oracle attempts to honor, therefore the actual period might be even higher to lower.
This is why when you queried tuned_undoretention from V$UNDOSTAT in some intervals it was identical to the UNDO_RETENTION value (xxxx) and in some other intervals it was lower than the UNDO_RETENTION value.

As for dbms_undo_adv.best_possible_retention, this function returns the best undo_retention based on the current settings while the tuned_undoretention in V$UNDOSTAT will tell you the actual period for which the undo retained based in every 10-mintue interval.

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
from v$undostat order by end_time;
SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;

If you use a non-extendable undo tablespace Oracle will ignore undo_retention and will attempt to hold onto all undo as long as it can based on demand for undo verse provided resource (undo tablespace size). if auto extend off and DML is long running it’s get snapshot too old ORA-1555 error. This is my recommendation for how the undo tablespace should be defined. Allocate a fixed size based on what you can afford to provide then monitor to see how you are using the allocated resource.