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_at_hotmail.com 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