Is it viable to set a very high undo retention, as to allow flashback queries several weeks back in time?

Naturally, enough space must be available in the undo tablespaces to contain the amount of undo data required. Are there other limitations I should be aware of?

What happens if there is not enough undo space available? Will production be affected in any way, or is it just a matter of flashback and rollback being limited (snapshot too old etc).


With a typical undo generation of a little less than 1 GB per day per instance, and up to 64 GB worth of undo space per instance it sounds viable to run with a 30-day undo retention target. No?


Although I haven't tried it, I would tend to suspect that you'll run into problems when your UNDO_RETENTION exceeds the length of time that Oracle maintains its SCN to timestamp mapping. If memory serves, that is roughly 1 week (well, if memory serves, it was 1 week in the 10g days and I'm not aware of anything that would have changed that in 11.2). I would tend to suspect that Oracle would use that mapping to determine which UNDO segments are old enough that they should be purged and that you would start to encounter problems if you exceeded that limit.

On my 11.2 system, for example, I can convert 7 days ago to an SCN and then back to a timestamp but not 8 days ago

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 7) ) from dual
SQL> /

06-NOV-12 PM

SQL> ed
Wrote file afiedt.buf

  1* select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
SQL> /
select scn_to_timestamp( timestamp_to_scn( systimestamp - 8) ) from dual
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1

I would tend to suspect on my system, therefore, that if I tried to retain 8 days of UNDO that Oracle would generally have a problem complying. It would take a week to verify one way or the other but if I had to wager, I would tend to wager that trying to hold more than a week of UNDO would fail.

Even if you could hold the UNDO that long, formulating the flashback query would be a major challenge since you couldn't specify a timestamp. You could potentially maintain your own (more long-term) mapping between SCN and timestamp by writing an automated job that would capture both every few seconds and hold them for weeks or months at a time and then specify AS OF SCN in your flashback queries. I'd tend to expect that the older UNDO would be gone but it might work.

  • Thanks for the tip, that certainly sounds like it is worth looking into! Although, to avoid using a time stamp you could possibly query for all versions of an object and identify the SCN of the version you want. – Roy Nov 13 '12 at 18:22
  • This is interesting. Shows you how often I go back over seven days, I can't go back further than 6.8 days. The Timestamp_To_SCN documentation does indeed say that the association length is limited, but it is "the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database". This seems to indicate that longer times are retained than we are seeing. – Leigh Riffel Nov 13 '12 at 18:40

Very high is a relative term that depends on perspective. You could mean 1 hour, 1 month, 1 year, etc. Assuming you mean something between one minute and one month, the answer will depend on how much undo is being produced and how much storage you have available. I haven't seen anything higher than two weeks.

Oracle automatically manages the undo space and will dynamically decrease the retention below the undo retention setting if space constraints require it. The caveat to that is when the undo tablespace has guaranteed retention.

The Oracle Database Administrator's Guide covers all this information on one page. Here are some excerpts:

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can optionally specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The exact impact this parameter on undo retention is as follows:

  • 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. See "Automatic Tuning of Undo Retention" for more information.

  • 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.


To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

  • "High" is indeed a relative term, which is why I specified it to "several weeks" which is slightly less so. If I understand you correctly, as long as retention guarantee is not enabled, once the undo tablespace is full Oracle will simply overwrite some unexpired undo. Thank you! – Roy Nov 13 '12 at 16:21
  • I didn't realize your "several weeks" was for the retention not just the flashback time frame. In any case, you are correct, without guaranteed retention you don't need to be concerned about causing a problem by setting the retention too high. – Leigh Riffel Nov 13 '12 at 18:12
  • Ah, although I might not see it all that clearly, undo retention target and intended flashback query window appears somewhat synonymous to me :) – Roy Nov 13 '12 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.