Determining Which Materialized Views Are Currently Refreshing

Refreshing a materialized view synchronizes data in the materialized view with data in its master table. You can query the V$MVREFRESH dynamic performance view to determine which materialized views are currently refreshing.

Before shutting down a database that contains materialized views, it is best to check if any materialized views are currently refreshing. If any materialized views are refreshing, then wait until all of the refreshes are complete before shutting down the database.

To determine which materialized views at a materialized view site are currently refreshing: 

1. Open SQL*Plus and connect to the materialized view site as the materialized view administrator.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Run the following query:

   COLUMN CURRMVOWNER HEADING 'Owner' FORMAT A15
   COLUMN CURRMVNAME HEADING 'Materialized|View' FORMAT A25
    
   SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH;
   

   The output will be similar to the following:

                   Materialized
   Owner           View
   --------------- -------------------------
   HR              COUNTRIES_MV
   HR              EMPLOYEES_MV
   

The V$MVREFRESH dynamic performance view does not contain information about updatable materialized views when the deferred transactions of the materialized views are being pushed to their masters.