X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • January 19, 2016

Why You Can Get ORA-00942 Errors with Flashback Query

Chris Saxon
Developer Advocate

I've recently been playing with one of my favorite Oracle features: Flashback. More specifically, Flashback Data Archive.

lightning last year by oompa123 (CC BY 3.0)
 
This extends the power of flashback query. Normally you can only use this to query as far back as your undo allows. With Flashback Data Archive (FBA), Oracle keeps a permanent store of a table's history (up to the retention period).
 
So using this you can run "as of" queries over a larger period. This enables you to see what was in a table days, months or even years ago! You can also view all the changes to a table between two times. Enabling FBA is a quick and easy way to audit data changes.
 
Even better, unlike standard flashback query, you can go back to a time before you ran DDL against the table.
 
And the best part? As of 12c it's FREE!*
 
But I found myself hitting a strange error. After creating the archive and adding a table to it, I was getting "ORA-00942: table or view does not exist" errors:
  SQL> select * from products as of timestamp sysdate - interval '1' minute;
  select * from products as of timestamp sysdate - interval '1' minute
                                                                   *
  ERROR at line 1:
  ORA-00942: table or view does not exist
Strange.

The table itself existed. So what's going wrong?

How Oracle enables flashback archive on a table

When you add a table to a flashback archive, Oracle creates extra tables in the background for you. It uses these to construct historic versions of the data.
 
You can find one of these tables with the following query:
  select * from sys.dba_flashback_archive_tables;

  TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NA  ARCHIVE_TABLE_NAME  STATUS
  ---------- ---------- -------------------- -------------------- ------
  PRODUCTS     CHRIS      FB_ARCHIVE         SYS_FBA_HIST_104694  ENABLED
The sys_fba_hist_* table is where the past data sits. Let's look at that table:
  SQL> select * from sys_fba_hist_104694;
  select * from sys_fba_hist_104694
                *
  ERROR at line 1:
  ORA-00942: table or view does not exist
What the… the history table isn't there!?
 
Of course, it's not my table that doesn't exist; it's the archive table that doesn't exist!

So why does that matter?

When you run a flashback query on an FBA enabled table, Oracle may also query the flashback tables. You can see these by looking at the execution plan for a flashback query:

So why did I get an error?

When you place a table in a flashback archive, Oracle does this in a separate process. Because of this, if it fails for any reason, you don't receive an exception!

How to fix it

In my case, I had forgotten to give my user a quota on the FBA tablespace. So the background process was unable to create the tables. So when I ran:
  select * from products as of timestamp sysdate - interval '1' minute;
Oracle was looking for sys_fba* tables that didn't exist!
 
To avoid this, ensure you grant the table owner has a quota allocated on the FBA tablespace. Also verify that there's space available in it. Once the user is setup correctly, disable and re-enable flashback archive on your tables.
 
Creating the history tables may fail for other reasons. So if you plan on using FBA, double check it's working before you rely on it.
 
If you managed to get yourself in this mess, you can get out of it by disassociating and reassociating the table with the FDA. See MOS note 1330817.1 if you need further details about this. Or if you're stuck, contact support!
 
If you'd like to have a go yourself, then you can use this script.
 
* This is also back ported to 11.2.0.4. Applies to Basic Flashback Archive only. Optimization requires EE license and the Advanced Compression Option.

Join the discussion

Comments ( 3 )
  • guest Wednesday, January 27, 2016

    Be aware that Flashback Data Archive relies on SYS.SMON_SCN_TIME for SCN to TIMESTAMP mappings. This object is actually a "cluster" and has a physical limit to the number of rows it can handle (based on db block size). If you are planning on enabling flashback archive to go back to review historical data (using the IMPORT_HISTORY function) you will need to contact Oracle Support to change this to a traditional table.

    Be aware that if you hit this limit (in our case 365,000 rows) in SMON_SCN_TIME, the database will throw ORA-02475, but will continue to function. From that point forward no mappings will be generated. This will render your flashback functionality inconsistent and will return bad data.

  • Chris Saxon Wednesday, January 27, 2016

    Thanks for the warning. Do you have any more information on how to avoid this?

  • guest Wednesday, January 27, 2016

    You can't. The only way to get in front of it is to turn SYS.SMON_SCN_TIME into a traditional table before you hit the wall. We've done that and are awaiting Oracle's response on when this goes into the base release and impacts on patching.

    My issue is that it traditionally only holds ~ 1440 rows... around 5 days... and now we have over 400,000 rows in the object as a table. Oracle confirmed it was built as a cluster in the first place for performance purposes... so I remain concerned about long term sustainability. As you probably know, SMON_SCN_TIME existed pre-Flashback Data Archive... so it likely has to go to the main RDBMS development group to address. I'm of the opinion at this point that there are huge issues with the FBA implementation and its reliance on SCN. Data portability... backup and recovery... distributed databases/replication... the fact that data pump is NOT FBA aware (just ignores the archives)... we've had issues with using FBA in a PDB... the list goes on.

    We also have another SR open around time gaps present in SMON_SCN_TIME that are unexplainable. All these issues seem to centre around using EXTEND_MAPPINGS and 'seeding' historical data into the FBA. We are just mid investigation on many of these issues...

    SR 3-11843367881 : TIMESTAMP_TO_SCN function returning inconsistent results

    SR 3-12076106371 : GAPS in timestamp_to_scn with Flashback Data Archive

    SR 3-11971833211 : PROACTIVE : Flashback Data Archive Unkowns

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.