Survey of MVs and MV Capabilities for 1 user

Log in as the user that owns the MVs and run the following:

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;
/