Author Archives: Rick

Oracle 11g Flashback Data Archive (FDA) query not returning results for some users

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.

A post about the fix that made this post possible

The first issue I ran into after re-installing WordPress  and attempting to create a post was that the edit field would not accept input.  It appeared to be disabled.

You know, that’s the thing I’m typing in now, so it’s kinda important.

After a bunch of digging, it turns out the solution is to modify the wp-config.php and add the following right above where it tells you to stop editing:

define('CONCATENATE_SCRIPTS', false);

Just wanted to pass that along.

Let’s try this again…

So a few weeks ago this site was hacked, but I don’t know how badly.  Rather than try to repair it, I decided to nuke the site from orbit and start over.  This site’s been pretty neglected so I didn’t lose much.  Let’s see how I do this time around.