Get a history of all refreshes of mview in Oracle 11g

2k Views Asked by At

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

1

There are 1 best solutions below

0
Roman On

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.