Checking Materialized View Compatibility with FAST REFRESH

This has been very helpful to me... I hope this will help some one out there as well :)

Materialized view can be FAST AUTO REFRESHED. There will be times when the one is unable to create a view due to some error that says, cannot create fast refresh view.

1st thing to do is execute the following script usually found in ../admin/utlxmv.sql

if not then can simply do a search for the above file.

Once the script has been executed. A proposed Materialized view compatibility with FAST REFRESH can be checked by executing the following Oracle package.

BEGIN

    DBMS_MVIEW.EXPLAIN_MVIEW ('Your complete MV script');
    COMMIT;
END;

Execute the above script to check the result as

SELECT * FROM MV_CAPABILITIES_TABLE;


Remember to delete data from this table before executing the package again.