This content has been marked as final. Show 6 replies
I do see doc saying that
SUMMARY VARCHAR2(1) Indicates whether this materialized view includes a GROUP BY clause or aggregation (Y) or not (N)
FULLREFRESHTIM NUMBER Approximate refresh time, in seconds, for full refresh (defined only when SUMMARY = Y)
INCREFRESHTIM NUMBER Approximate refresh time, in seconds, for fast refresh (defined only when SUMMARY = Y)
-> But for some of the mviews that have SUMMARY defined as Y the refresh times is still 0
Oracle recommends that you refer to ALL_MVIEWS for this information instead of USER_MVIEW_ANALYS.
How do you refresh ?
(JOB, DBMS_MVIEW.REFRESH, REFRESH clause)
The FULLREFRESHTIM & INCREFRESHTIM is not in ALL_MVIEWS . For this we have to refer to the DBA_MVIEW_ANALYSIS - Metalink ID ID 1513554.1 also suggests the same.
The other question brings an interesting point. I just verified that since the last couple of days, due to some major changes, we had used drop / create. Probably thats why it is all 0's.
Check the time difference between LAST_REFRESH_DATE and previous LAST_REFRESH_DATE in ALL_MVIEWS for a particular view.
(the time should be increased by mview refresh duration)
I am trying to find out how long the last materialized view refresh run?
SELECT OWNER,mview_name, last_refresh_date, refresh_method, fullrefreshtim, increfreshtim, summary
I tried using above query. But as per oracle doc, FULLREFRESHTIM or INCREFRESHTIM column returns the refresh time only when value of the SUMMARY is defined as “Y”. In our case for all the materialized views the summary column value is N. The value of increfreshtim column is zero for most of the materialized views. Also for some of the materialized views the value for fullrefreshtim column is greater than zero even if the refresh method is fast. For some of the fast refresh Mviews value of fullrefreshtim and increfreshtim is greater than 0.
Why fullrefreshtim is greater than 0 for fast refresh Mviews?
Can some one please help with this issue?