Undo retention time with autoextend on and autoextend off in Oracle

The undo tablespace can have many datafiles, but if it has at least 1 datafile with autoextend=on then the tablespace will use the first approach, it will set the undo retention time based on statistics of the database gathered. MMON will calculate the value of "tuned_undoretention" based on the "maxquerylen" that checks every 30 sec interval.

Oracle recommends do not have few datafiles with autoextend enabled and disabled in others on Undo tablespaces because this could make the database miscalculate the undo retention time.

Example 1: Autoextend = on

The undo tablespace is "UNDOTBS1" and it has only 1 datafile with autoextend=on

SQL>  select file_name, autoextensible from dba_data_files where tablespace_name='UNDOTBS1'

FILE_NAME                        AUTOEXTENSIBLE
——————————– ————–
+DATA/orcl/undotbs01.dbf           YES

Using this approach, I can see that the current undo retention time calculated is the following:

SQL> SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;

TUNED_UNDORETENTION
——————-
1005

Example 2: Autoextend = off

But, when we have the tablespace with autoextend off, the database will set the undo retention as the maximum value possible, so you could see a value so high if you have a large undo tablespace (don't be afraid of it), and this will result in a lot of unexpired extents. If you are seeing that your undo tablespace is almost 100% used and most of that percentage is used by "unexpired" segments, don't worry, Oracle will reuse all these unexpired extents unless you have retention guarantee enabled, but usually this is not enabled.

SQL> alter database datafile '+DATA/orcl/undotbs01.dbf' autoextend off;

Database altered.

SQL> select file_name, autoextensible from dba_data_files where tablespace_name='UNDOTBS1';

FILE_NAME                   AUTOEXTENSIBLE
————————— ————–
+DATA/orcl/undotbs01.dbf    NO

Checking the retention time:

SQL> SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;

TUNED_UNDORETENTION
——————-
4268

As you can see now the value is higher. The value is not too high because the datafile has only 5MB.

So remember, regardless if you are using autoextend=on or autoextend=off, what it is really important is if you are using auto undo management (undo_management=auto). If you are using it then ALWAYS your parameter "undo_retention" will be ignored, ALWAYS. Oracle will use the calculated value based on statistics (autoextend=on) or based on the whole size of the tablespace (autoextend=off), and undo_retention will work only as the "minimum" time, and oracle will do the best to honor it. 

So if you want to know what is the real undo retention time run the following query:

SQL> SELECT BEGIN_TIME , TUNED_UNDORETENTION FROM V$UNDOSTAT ORDER BY BEGIN_TIME;