0

In Oracle12+ one can query a handy view called dba_mvref_stats to find all refreshes of a particular mview. I've bumped into the same task on Oracle 11g and I'm kinda stuck at the moment.

There's a similar question (History refresh of materialized view) but IMHO the answer doesn't responds the author's question as ALL_MVIEW_REFRESH_TIMES reflects refresh time of underlying table and keeps only latest refresh type.

What I'm looking for is an answer to question "Was there any COMPLETE refresh of a particular mview?". I create an mview on prebuilt table (which is empty) and want to run a COMPLETE refresh if it hasn't been run before or continue with a FAST refresh.

Maybe other options exist for me, so feel free to advise any.

EDIT: Question Materialized Views - Identifying the last refresh is about the last refresh event while I want to know the whole history of refreshes

Roman
  • 473
  • 5
  • 22
  • If you've just created the view on empty table, then run complete refresh after this, else schedule fast refresh. What is the reason to complicate this task? – astentx May 04 '21 at 11:57
  • The problem is that mview can be refreshed via 2 different jobs and I don't know which one will do it. Plus it happens inside of a stored procedure so I have to put a logic that detects if complete refresh needed otherwise it runs fast refresh. – Roman May 04 '21 at 12:01
  • @astentx I think I can check `all_mviews.last_refresh_type` whether it has `NA` value or not. If it does then do a complete refresh otherwise do a fast refresh. – Roman May 04 '21 at 12:03
  • Does this answer your question? [Materialized Views - Identifying the last refresh](https://stackoverflow.com/questions/5798894/materialized-views-identifying-the-last-refresh) – astentx May 04 '21 at 12:53
  • Well, considering that 11g is out of support ..... perhaps the more pressing issue would be upgrading to a supported version .... – EdStevens May 04 '21 at 14:20

1 Answers1

0

Looks like the answer to the question "How does one get a HISTORY of all refreshes?" is "You can't" for Oracle 11g and "Yes, just query (dba/user)_mvref_stats view" for Oracle 12c+.

Also in case of Oracle 11g consider answering "What was the last refresh type/date?" instead of the question above. In this case you can check on all_mviews (and especially last_refresh_type).

Maybe it will help someone someday.

Roman
  • 473
  • 5
  • 22