At times, you may find out or feel that UNDO_RETENTION is useless at all. Raising the number of seconds cannot resolve ORA-01555. This is true under some specific conditions. Here I quote the requisites from the official document below:
Fix-sized Undo Tablespace
The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database always tunes the undo retention period for the best possible retention, based on system activity and undo tablespace size.
Autoextend Undo Tablespace
For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information. The UNDOTBS1 tablespace that is automatically created by DBCA is auto-extending.
In other words, if UNDO is automatically extending space a lot more than you expected, you can do nothing until it reaches its maximum size or you can do something like the following.
In that moment, I believe that most of undo segments are not ACTIVE, they are just UNEXPIRED. Meanwhile, SMON is trying to honor UNDO_RETENTION, so automatically extending UNDO space is its first choice.
If you don’t want it to extend space any longer, an immediate cure is to set a smaller UNDO_RETENTION within memory scope, just temporarily. After that, most of UNEXPIRED undo segments become EXPIRED, SMON starts to reclaim space and the size of UNDO tablespace stops growing.
Further reading: How to Check UNDO Tablespace Usage
For seeking appropriate UNDO_RETENTION, you can use Oracle supplied functions like the followings:
SQL> select dbms_undo_adv.best_possible_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;
SQL> select dbms_undo_adv.longest_query(sysdate-7, sysdate) UNDO_RETENTION from dual;
SQL> select dbms_undo_adv.required_retention(sysdate-7, sysdate) UNDO_RETENTION from dual;
SQL> select dbms_undo_adv.rbu_migration(sysdate-7, sysdate) UNDO_RETENTION from dual;
In the above, I queried the possible values of UNDO_RETENTION for the last 7 days. Please note that, one instance would return different values from the other instance in a RAC system. This is because different instances use different UNDO tablespace.
Here I make a matrix to show how space types correlate with RETENTION GUARANTEE. Let’s see how UNDO_RETENTION changes under different conditions.
|Fixed-Sized||Is ignored||Is guaranteed|
until no free space is left
|Autoextend||Is honored||Is guaranteed|
until MAXSIZE is reached
As you can see, UNDO_RETENTION highly depends on the state of space management.