메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Why You Can Get ORA-00942 Errors with Flashback Query

By Chris Saxon-Oracle on Jan 19, 2016

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

 

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.

 

출처 : https://blogs.oracle.com/sql/entry/why_you_can_get_ora

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 12
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 50
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 113
25 How to change the database name in 12c 명품관 2016.05.31 46
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 86
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 77
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 99
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 85
20 Quick tip on Function Based Indexes 명품관 2016.04.19 35
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 99
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 71
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 240
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 34
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 48
» Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 58
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 39
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 28
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 11
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 148
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 60
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 17
위로