How To Find Last Refresh Time of Materialized Views

dbtut

6 years ago

The following queries can be used to determine when materialized views were last refreshed.

For all times:

SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES;

1	SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES;

For a specific time interval:

SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh between '29-OCT-10' AND '31-OCT-10';

1	SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh between '29-OCT-10' AND '31-OCT-10';

SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh > '28-OCT-10';

1	SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES where last_refresh > '28-OCT-10';

The sample output would be as follows.

SQL> set pages 1000
SQL> set lines 1000
SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES;

OWNER			       		   NAME			                  MASTER_OWNER		             MASTER			                LAST_REFR
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
SYSMAN			               MGMT_ECM_MD_ALL_TBL_COLUMNS    SYSMAN			             MGMT_ECM_SNAPSHOT_MD_COLUMNS   05-DEC-16

SQL> set pages 1000

SQL> set lines 1000

SQL> SELECT * FROM ALL_MVIEW_REFRESH_TIMES;

OWNER NAME MASTER_OWNER MASTER LAST_REFR

------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------

SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS SYSMAN MGMT_ECM_SNAPSHOT_MD_COLUMNS 05-DEC-16