Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is there a way to list the refresh dates/times of materialized views for the last 5 days?

CatinredbootsFeb 14 2018 — edited Feb 15 2018

Hello,

I was just wondering if there is way to list the times and dates of Materizalized views refreshes. I do know how to check the last refresh from dba_mview_analysis view but I am not able to do it for the last 5 days for example.

This is the query I'm using for the last refresh date and time and it tells me start and end date/time:

set linesize 200

set pagesize 200

  SELECT

   mview_name,

   last_refresh_date "START_TIME",

   CASE

      WHEN fullrefreshtim <> 0 THEN

         LAST_REFRESH_DATE + fullrefreshtim/60/60/24

      WHEN increfreshtim <> 0 THEN

         LAST_REFRESH_DATE + increfreshtim/60/60/24

      ELSE

         LAST_REFRESH_DATE

   END "END_TIME",

   fullrefreshtim,

   increfreshtim

FROM dba_mview_analysis

WHERE owner='schema1'

order by END_TIME desc, FULLREFRESHTIM desc;

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 15 2018
Added on Feb 14 2018
13 comments
2,618 views