Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dario.

Asked: April 21, 2017 - 9:05 am UTC

Last updated: April 26, 2017 - 4:12 am UTC

Version: Oracle EE 12

Viewed 1000+ times

You Asked

Hi,

During maintenance of fast refresh materialized views, 10000 MLOG$ records are lost. Can you suggest what are our options for restoring those missing records to our MV?

Making dummy update of those records on master table is not allowed due to license policy.

Br,
Dario

and Connor said...

"Making dummy update of those records on master table is not allowed due to license policy."

I dont really understand that statement.

But if

a) your mviews are out of alignment with your source, and
b) you can't artificially make changes to seed the logs

then your only option would be to recreate the mviews.

Rating

  (2 ratings)

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

Comments

Dario Nikolic, April 24, 2017 - 9:15 am UTC

Recreate MVIEWS is a problem due to:
- huge amount of data in master table
- tiny DB link with forbidden parallelism

Is there some option with manualy inserting missing records into MLOGS$ or MVIEW?
Connor McDonald
April 26, 2017 - 4:12 am UTC

You could *test* the following scenario:

- drop mview using the preserve table clause
- make the changes to the table
- recreate the mview using the PREBUILT table clause


Dario Nikolic, April 26, 2017 - 8:50 am UTC

Create MV on preserved table clears MLOG$ which causes aditional loss of changes.

This can be avoided with:
alter materialized view log on TABLE PURGE NEXT SYSDATE + 100


After MV is restored we can return on purge immediate
alter materialized view log on TABLE PURGE IMMEDIATE

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.