Flashback isn't, er, flashing.

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 25 May 2004 16:55:34 +1000
Message-ID: <40b2edd5$0$1588$afc38c87@news.optusnet.com.au>

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 Received on Tue May 25 2004 - 01:55:34 CDT