Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, KEVIN.

Asked: January 15, 2018 - 2:31 am UTC

Last updated: January 15, 2018 - 11:03 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Please refer to the LiveSQL link. NB some of the statement do not work because the user has insufficient privs. to create and manage Flashback areas in the LiveSQL environment.

The code creates a table, inserts data , creates a flashback archive then flashes-back the data.

However, after applying a materialized view to the base table (statement 8), the flashback no longer works ( on statement 9). This is well-documented by Oracle and there appears no work-around apart from dropping the materialized view.

My question is, why is the Flashback operation prevented by the Materialized View? Especially in the instance where the MV is only refreshed completely and not immediately during the flashback. A follow-up question would is be, is there any way a table associated with FBA can have a materialized view ( with particular parameter settings)

Many thanks
Kevin Payne


with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/f4s6tewq7gdv760vs9vjx704b

and Chris said...

It doesn't work because it's not implemented! ;)

The problem is the general case of a fast refreshable MV with more than one table.

If you flashback one of the tables to before the last refresh, what do you do? What should happen to the MV logs?

Sorting this out adds lots of complexity. So we just sidestep the whole issue of tables with MVs.

Note this is an (internal) enhancement request to support this already. If you have a compelling business reason why you need this, I suggest you log your own too.

You could also add it to the Database Ideas forum to gather community support:

https://community.oracle.com/community/database/database-ideas/content

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Flashback table archive and materialised views

Kevin Payne, January 15, 2018 - 8:58 pm UTC

Thanks for the prompt reply, Chris.

We have an internal schema full of reference data tables that we on demand publish using materialised views ( mainly single table views).

We also want to replace the current trigger based / application audit table logging with FBA. Which all seems to work fine apart from the on demain recovery requirement using flashback table. Weve adopted a work around ( dml delete base table records followed by insert select ..from as of timestamp ) which works but is slow.

I will log an internal request and also post on the community site

Thhanks again!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.