RE: Strange Flashback Query Behaviour

  • From: jonathan@xxxxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 14 Feb 2012 12:05:03 +0000


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 

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             SELECT  SCN_WRP, SCN_BAS, TIME_DP
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     There is a quirk around creating or changing tables: you need to wait a 
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 

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.

Jonathan Lewis

mccmx@xxxxxxxxxxx wrote:
> > Don't forget that 11.2 gives you deferred segment creation by default, =
> and this may complicate the issue. Try the test with segment creation immed=
> iate; and if you get the same error try creating the table, then waitin=
> g about 3 minutes before inserting. There used to be some interesting granu=
> lairty issues with SCNs and DDL.
> >
> > Regards
> > Jonathan Lewis
> I just tried the test case with "SEGMENT CREATION IMMEDIATE" and the same b=
> ehaviour exists.  Plus the problem exists on too.
> Its funny you should mention waiting between the CREATE and the INSERT beca=
> use our current workaround is to add a dbms_lock.sleep for 30 seconds after=
>  the CREATE and this fixes the problem.  Do you have more details about t=
> he SCN granularity issues you mentioned..?
> I've logged this as a bug with Oracle but maybe this is expected behaviour =
> under certain circumstances.
> Matt                                    =


Other related posts: