When a materialized view is no longer needed, you can drop it.
If you drop a materialized view, all views dependent upon it become INVALID. To use the dependent views, you need to either change the definition for the dependent views, or recreate the dropped materialized view.
To determine whether there are views dependent on a materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.
For information on how dropping materialized views affects view alterations, see View dependencies.
Connect to the database as the DBA, or as the owner of the view.
Open the Views folder for that database.
Select the materialized view and then choose File > Delete.
Connect to the database as the DBA, or as the owner of the view.
Execute a DROP MATERIALIZED VIEW statement.
The following statement drops the EmployeeConfidential materialized view.
DROP MATERIALIZED VIEW EmployeeConfidential;