Refresh Materialized Views in Oracle Database

1. Check stale Materialized view.

SQL> select OWNER,MVIEW_NAME,STALENESS,LAST_REFRESH_TYPE,LAST_REFRESH_DATE,COMPILE_STATE from dba_mviews ORDER BY MVIEW_NAME;

SYSMAN  MGMT$ARU_PATCH_RECOM_MD STALE  COMPLETE 03-MAY-17 VALID

2. Use DBMS_MVIEW.REFRESH procedure to refresh them.

SQL> EXEC DBMS_MVIEW.REFRESH('SYSMAN.MGMT$ARU_PATCH_RECOM_MD', 'C', '',TRUE,FALSE,0,0,0,FALSE,FALSE);

PL/SQL procedure successfully completed.

3. Verify the status of staleness. 

SQL> select OWNER,MVIEW_NAME,STALENESS,LAST_REFRESH_TYPE,LAST_REFRESH_DATE,COMPILE_STATE from dba_mviews ORDER BY MVIEW_NAME;

    SYSMAN  MGMT$ARU_PATCH_RECOM_MD  FRESH   COMPLETE 04-OCT-19  VALID