Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Undo Retention of 5 days; anyone?

Re: Undo Retention of 5 days; anyone?

From: Daniel W. Fink <Daniel.Fink_at_Sun.COM>
Date: Sun, 15 Feb 2004 21:24:09 -0700
Message-id: <>

The smon_scn_time table maintains a mapping of scns to timestamps (at 5 minute granularity) for up to 5 days of instance uptime (not wall clock time). When a flashback query is parsed, the scn supplied (or the time) is checked to see if it is newer than the oldest in the table. If it does not exist, the query returns an error.

Regardless of the setting of undo_retention, fbq is dependent upon there being complete undo. If any link in the undo chain has been removed, the fbq will fail. undo_retention is not a guarantee, though the Automatic Undo Management algorithms will use 'unexpired' blocks before using 'expired' blocks. Of course, this requires that Oracle correctly monitors the expire times (which I have very serious doubts that they do).

BTW, automatic undo management is not a requirement for flashback query. You can do fbq on good ol' rollback segments...

Any application that has a 'requirement' for any duration of flashback should not depend on the Oracle mechanism as it is far from guaranteed.


Indy Johal wrote:

> Arup
> If I am correct, can we go beyond 5 days of Flashback recovery using
> Timestamp method in Oracle 9i. I know somebody referred earlier in the
> mail that he recover the data which is older than 15 days. I know that
> some documentation says that it is not possible as timestamp mapping to
> SCN is maintained in SMON_SCN_TIME for only 5 days. I am not having any
> configuration to test it so thought I can check with you.
> Thanks
> Indy Johal
> (201) 946-5687 [W]
> (201) 400-3960 [M]
> "We tell your story to the world."

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Sun Feb 15 2004 - 22:24:09 CST

Original text of this message