Hello Gurus,
- Oracle 11g
I am trying to determine the last refresh date and timestamp of a materialized view. I have suspected my materialized view is not refreshing at the desired scheduled time, so I have been spooling a file out to my local machine with the results. Here are my findings.
If I execute the query below, it shows the date and timestamp of the last recorded refresh.
Query
select mview_name, to_char(last_refresh_date, 'yyyy-MON-dd hh:mi:ss AM') as "UTC", from_tz(cast(last_refresh_date as timestamp), 'UTC') at time zone 'America/Chicago' as "Local"
from all_mviews
where mview_name = 'table1';
Results
| MVIEW_NAME | UTC | Local |
| table1 | 2017-MAR-29 05:26:09 AM | 2017-MAR-29 12.26.09.000000000 AM AMERICA/CHICAGO |
According to the results, it was last refreshed after midnight on the 29th of March 2017 (local time). However, the spooled file tells me a different story.
Below are both files that were spooled. Under the "Name" column, I have the date and timestamp of the database, which is set to UTC. Under the "Date modified" column, it is the local time of my machine (US/Central).
Here are the contents of both files.
The spooled files indicate that an update was performed between the hours of 6:55 AM - 7:56 AM (UTC), which is 1:55 AM - 2:56 AM (local time). The query from "ALL_MVIEWS" indicate that the last refresh was at 5:26:09 AM (UTC), which was 12:26:09 AM (local time). Because the time from "ALL_MVIEWS" does not fall between the spool file time, why is the discrepancy? Any help is greatly appreciated. Thanks!
Edit--
One other item I forgot to mention, the materialized view is supposed to be scheduled to refresh daily @ 5:00 AM (local time, which is US/Central for me). This is the requested time I asked our DBAs to use. The refresh type is supposed to be COMPLETE and not FAST.
Message was edited by: 976563