RE: Strange Flashback Query Behaviour

From: Matt McClernon <>
Date: Tue, 14 Feb 2012 21:43:33 +0000
Message-ID: <BAY171-W6F2AC8C8DC2230C88A870B77C0_at_phx.gbl>

> > Matt,

> The last note I wrote about this issue is dated June 2002, and I don't think I've looked at the problem since then. The comments in the test script say the following:
> rem Flashback has a granularity of about 5 minutes as it seems to
> rem be smon that maintains a table of 'legal values' for the
> rem flashback process. This is reasonable for TIME, but why does
> rem it do the following query even when an SCN is quoted:
> rem
> rem from SMON_SCN_TIME
> rem where SCN_WRP* 4294967295 + SCN_BAS <= (:1 * 4294967295 + :2)
> rem ORDER BY
> rem SCN_WRP, SCN_BAS desc
> rem
> rem There is a quirk around creating or changing tables: you need to wait a short
> rem period after the create table, otherwise your attempts to flashback raise error:
> rem ORA-01466: unable to read data - table definition has changed
> Things have moved on - Oracle 11.1 seems to have hidden some of its recursive activity from a simple SQL trace, for example, and I know that the table now holds details down to a finer granularity - possibly 3 seconds - in a raw column.
> It's possible that the recursive SQL that Oracle uses to parse and optimize your query starts by checking the data dictionary 'as at scn', and finds that the smon_scn_time entry for the last five minutes doesn't exist (in the database) yet - resulting in a failed flashback on the data dictionary because at the most recent known timestamp/scn the table didn't exist. I'd have to do some more work to figure out the details of how this might work but I think I'm guessing in roughly the right direction.
> Regards
> Jonathan Lewis

This is excellent, thanks very much for the feedback.  We were trying to determine the extent of the problem because if this impacts standard DML then we cannot base our development of a new application on this functionality.  However if it just impacts DML that occurs close to DDL operations then we can work around that.

Your feedback has further solidifies our thinking on this, thanks.

During my testing I noticed that the following (solitary) recursive SQL is being written to the 10046 trace:

select time_mp, scn, num_mappings, tim_scn_mapfromsmon_scn_time   where scn  =    (select max(scn) from smon_scn_time where time_mp <= :1) which is similar to the one you mentioned.  I suspected that this SQL was the root of the problem. Thanks again for your valuable help.

                                               -- Received on Tue Feb 14 2012 - 15:43:33 CST

Original text of this message