Query to check the latest Materialized View refresh time and type

on

 Please find the query to check the last mview refresh time and type.

set lines 999
set pagesize 999
col LAST_REFRESH_TYPE for a17
col OWNER for a10
col MVIEW_NAME for a40
SELECT owner,
       mview_name,
       last_refresh_type,
       TO_CHAR(last_refresh_date, 'MM/DD/YY HH24:MI:SS') starttime,
       TO_CHAR (last_refresh_end_time,'MM/DD/YY HH24:MI:SS') endtime,
       Round(( last_refresh_end_time - last_refresh_date ) * ( 60 * 24 ), 2)
       runtime
FROM   dba_mviews
WHERE  mview_name LIKE '%&MVIEW_NAME%';
Sample Output: 


OWNER   MVIEW_NAME      LAST_REFRESH_TYPE   STARTTIME         ENDTIME              RUNTIME
------- --------------- ------------------- ----------------- ----------------- ----------
APPS    XXDBA_ORACLE_MV   COMPLETE        07/23/22 03:00:01 07/23/22 03:16:43       16.7
APPS    XXDBA_DBA_MV      COMPLETE        07/23/22 05:00:00 07/23/22 05:22:06       22.1
APPS    XXLDBA_ARENA_MV   COMPLETE        07/23/22 03:00:01 07/23/22 03:24:01         24

Comments

Post a Comment