Materialized Views - Identifying the last refresh

Question

I presently access a series of views and materialized views. The materialized are maintained by a third party who offers little information regarding the frequency and success of the materialization. Of late the materialized views have failed to refresh and I have sent out numerous reports with incorrect/delayed data contained within.

At present I am querying each materialized I intend to use to establish when the latest update occurred within the transactional system, if it has not been refreshed then the rest of the code does not execute, however this a lot of wasted effort and can sometimes lead to an incorrect assumption (the materialized view may have been refreshed, but there were no additional transactions made - therefore the remainder of the code does not execute) and I would prefer another method.

Is there a way to identify whether a materialized view has been refreshed using an Oracle system table? If not, does anyone have any ideas how I would do this without having to contact the third party?

dba_mviews...bt are you sure that you have access to sys tables.. — uncaught_exceptions, Apr 27 '11 at 03:15
@doc_180 I thought I had access to them, but I am unable to access the one you have suggested. I can access all_tables and all_views. As you might have guessed I don't have that much experience with oracle. — Scott, Apr 27 '11 at 03:24
check all_mviews options. If you could not see the mv you are interested in.. you do not have permission. — uncaught_exceptions, Apr 27 '11 at 03:27
@DOC_180 you are a gem that is exactly what I was after. Thank you so much. — Scott, Apr 27 '11 at 03:34
No problem, please add this as answer and mark it as answered. It will help someone else... — uncaught_exceptions, Apr 27 '11 at 03:36
Are you able to run this **select mview_name, LAST_REFRESH_DATE from user_mviews** — V4Vendetta, Apr 27 '11 at 07:36

score 57 · Accepted Answer · answered Apr 27 '11 at 09:53

It looks like doc_180 already answered this in the comments, I'm not sure why it didn't get added as the answer

SELECT owner, mview_name, last_refresh_date
  FROM all_mviews
 WHERE owner = <<user that owns the materialized view>>
   AND mview_name = <<name of the materialized view>>

If you have access to the DBA tables, you could substitute DBA_MVIEWS for ALL_MVIEWS. That would allow you to get access to information about when every materialized view was refreshed rather than just the subset of materialized views that you have access to. Of course, that's probably not a difference that is particularly important in this case.

score 7 · Answer 2 · answered May 30 '14 at 11:29

7

Unfortunately oracles default date format is YYYY-MM-DD. If you need the time just use something like this:

SELECT owner, mview_name, to_char(LAST_REFRESH_DATE, 'yyyy-mm-dd hh24:mi:ss') last_refresh_date
FROM all_mviews
WHERE owner = 'FOO'
AND mview_name = 'MV_BAR';

answered May 30 '14 at 11:29

ptr

97
1
1

I was wondering if the date was longer. Thanks for the additional answer. – John K. N. Jun 01 '16 at 09:13

Hugo Rodrigues · Answer 3 · 2016-05-13T10:17:00.217

3

Personally i use the all_snapshots, and this information is complementary to the information of the DBA_MVIEWS or ALL_MVIEWS

select owner, name, last_refresh, error, status, refresh_mode  
from  all_snapshots 
where owner = 'owner';

edited May 13 '16 at 10:17

answered May 13 '16 at 10:11

Hugo Rodrigues

56
2

where can we get MV Creation date? – Rupasa Sushma Jun 24 '20 at 13:55

Materialized Views - Identifying the last refresh

3 Answers3

Linked