RE: Strange Flashback Query Behaviour
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 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.
> > 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 18.104.22.168 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 =