Survey of MVs and MV Capabilities for 1 user

select * from mv_capabilities_table;


BEGIN
    FOR c1 IN (SELECT mview_name FROM user_MVIEWS)
    LOOP
        DELETE FROM MV_CAPABILITIES_TABLE WHERE STATEMENT_ID='DMANN';
        dbms_mview.explain_mview (c1.mview_name,'DMANN');

        FOR c2 in (SELECT capability_name,  
                          possible, 
                          SUBSTR(related_text,1,8) AS rel_text, 
                          SUBSTR(msgtxt,1,60) AS msgtxt
                     FROM MV_CAPABILITIES_TABLE
                    WHERE STATEMENT_ID='DMANN'
                    ORDER BY seq)
        LOOP
            DBMS_OUTPUT.PUT_LINE(c2.CAPABILITY_NAME||c2.POSSIBLE||c2.REL_TEXT||c2.MSGTXT);        
        END LOOP;

    END LOOP;
END;

History of MV Refresh Related SQL

-- MV Refresh info over all AWR history available 
WITH MV_SQL as (
         SELECT SQL_ID, 
                SQL_TEXT 
           FROM DBA_HIST_SQLTEXT 
          WHERE SQL_TEXT LIKE '%MV_REFRESH (%'),
     MV_ACTIVITY as (
         SELECT MV_SQL.SQL_ID, 
                SUM(DBA_HIST_SQLSTAT.EXECUTIONS_TOTAL) as TotalExecs,
                SUM(DBA_HIST_SQLSTAT.ELAPSED_TIME_TOTAL/1000000) as TotalTime
           FROM DBA_HIST_SQLSTAT, MV_SQL
          WHERE DBA_HIST_SQLSTAT.SQL_ID=MV_SQL.SQL_ID
          GROUP BY MV_SQL.SQL_ID)
SELECT MV_ACTIVITY.SQL_ID,
       MV_ACTIVITY.TotalExecs,
       ROUND(MV_ACTIVITY.TotalTIme,1), 
       ROUND(TotalTime/TotalExecs,1) as SecondsPerExecution, 
       MV_SQL.SQL_TEXT 
  FROM MV_SQL, MV_ACTIVITY 
 WHERE MV_SQL.SQL_ID=MV_ACTIVITY.SQL_ID
 ORDER BY 4 DESC;

Check MView Logs for Pending Changes

-- Check the number of pending MVIew changes 
SET SERVEROUTPUT ON
DECLARE
  my_num_rows NUMBER;
  my_sql VARCHAR2(256);
BEGIN

  FOR cur IN (SELECT LOG_OWNER, LOG_TABLE FROM ALL_MVIEW_LOGS WHERE LOG_OWNER LIKE UPPER('&1'))
  LOOP
    my_sql := 'select count(*) from '||cur.log_owner||'.'||cur.log_table;
    EXECUTE IMMEDIATE my_SQL INTO my_num_rows;
    IF my_num_rows > 0 THEN
      DBMS_OUTPUT.PUT_LINE(cur.LOG_TABLE || ' has ' || my_num_rows || ' records.');    
    END IF;
  END LOOP;
  
END;
/