Re: Flashback isn't, er, flashing.

Howard J. Rogers wrote:

> Can anyone confirm a bug in 9.2.0.1 regarding table-level flashback?
>
> Oracle 9i, Release 2, no patch (9.2.0.1) Windows 2000.
>
> At 7.00am, I updated EMP so that all salaries ($290,000ish of them) were
> divided by 10. Committed. Later....
>
> SQL> exec
> dbms_flashback.enable_at_time(TO_TIMESTAMP('25-05-2004:06:33:00','DD-MM-YYYY
> :HH24:MI:SS'))
> PL/SQL procedure successfully completed.
>
> SQL> select sum(sal) from emp;
> SUM(SAL)
> ----------
> 290250
>
> Which is correct, showing as it does the pre-updated salary totals.
>
> BUT....
>
> SQL> select sum(sal) from emp as of
> timestamp(to_timestamp('25-05-2004:06:33:00','DD-MM-YYYY:HH24:MI:SS'));
> SUM(SAL)
> ----------
> 2902.5
>
> Which is the salary totals *after* the update, and not before, even though I
> am asking to select from the table as at exactly the same time (6.33am) as
> in the first example.
>
> In other words, session-based flashback works perfectly. But table-level
> flashback for exactly the same time doesn't. In desperation, I then tried
> this on 9.2.0.4 RHAS21, and it worked perfectly (different times and data
> values of course):
>
> SQL> select sum(sal) from emp;
> SUM(SAL)
> ----------
> 5805
>
> SQL> exec
> dbms_flashback.enable_at_time(to_timestamp('25-05-2004:17:00:00','DD-MM-YYYY
> :HH24:MI:SS'))
>
> PL/SQL procedure successfully completed.
>
> SQL> select sum(sal) from emp;
> SUM(SAL)
> ----------
> 29025
>
> (Session-level flashback working fine at 17:00)
>
> SQL> exec dbms_flashback.disable
> PL/SQL procedure successfully completed.
>
> (Session-level flashback disabled)
>
> SQL> select sum(sal) from emp as of
> timestamp(to_timestamp('25-05-2004:17:00:00','DD-MM-YYYY:HH24:MI:SS'));
> SUM(SAL)
> ----------
> 29025
>
> Table-level flashback at 17.00 working perfectly.
>
> Now I don't know whether this is because the non-working example is on
> Windows, 9.2.0.1, or both. Or whether it works in Linux just because it's
> Linux or because it's been patched, or both. Too many degrees of freedom. So
> I had a look at Metalink and could find nothing. Anyone know?
>
> Regards
> HJR
Didn't I make some off-hand comment awhile back about being at 9.2.0.1? ;-)

If you want to try my demos ... and I know they work in 9.2.0.4 they are at: http://www.psoug.org/reference/dbms_flashback.html

HTH

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)