Once upon a time, we were seeing inconsistent results when using Flashback Data Archive (FDA) on an Oracle 11g R2 database.   We had created an FDA and enabled it for a few tables.  User A owned the tables. User B had SELECT and FLASHBACK privileges to the tables in schema A. We inserted a row into table A.X with FDA enabled.

When user A performed SELECT * FROM X AS OF TIMESTAMP SYSTIMESTAMP  they would see the row.

When user B performed  SELECT * FROM A.X AS OF TIMESTAMP SYSTIMESTAMP the query returned 0 rows.

If we remove the “as of” clause and had user B perform SELECT * FROM A.X they saw the rows.

Both users had the same timezone settings. While trying to diagnose the problem with user B, we created user C, also with SELECT and FLASHBACK privs on A.X. The query for user C returned rows.

Thinking something was wrong with user B, we re-created user B. When we first tested it, the query for user B worked! After a couple of minutes, the query stopped returning rows again. Also, running the query for user A (the table owner) also returned 0 rows after a while. The query for user C still returned rows.   WTF?

Here was the solution:

When we examined the SYS.SMON_SCN_TIME table, we found several records where the TIME_DP values were far in the future (the year 2030). At one point a system administrator set the system clock incorrectly, and corrected it several minutes later.

We disabled FDA on the tables, and dropped the FDA and it’s tablespace. We then deleted all records from the SYS.SMON_SCN_TIME table. We created the FDA again and re-enabled it for the tables.

The FDA behaved as expected.