RE: Strange Flashback Query Behaviour

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



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 11.1.0.7 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                                    =
> 

--
http://www.freelists.org/webpage/oracle-l


Other related posts: