DROP MATERIALIZED VIEW

Purpose

Use the DROP MATERIALIZED VIEW statement to remove an existing materialized view from the database.

Note: The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

See Also: CREATE MATERIALIZED VIEW for more information on materialized views, including a description of the various types of materialized views ALTER MATERIALIZED VIEW for information on modifying a materialized view Oracle9i Replication for information on materialized views in a replication environment Oracle9i Data Warehousing Guide for information on materialized views in a data warehousing environment

Prerequisites

The materialized view must be in your own schema or you must have the DROP ANY MATERIALIZED VIEW system privilege. You must also have the privileges to drop the internal table, views, and index that Oracle uses to maintain the materialized view's data.

See Also: DROP TABLE, DROP VIEW, and DROP INDEX for information on privileges required to drop objects that Oracle uses to maintain the materialized view

Syntax

drop_materialized_view::=

Text description of drop_materialized_view

Keywords and Parameters

schema

Specify the schema containing the materialized view. If you omit schema, Oracle assumes the materialized view is in your own schema.

materialized_view

Specify the name of the existing materialized view to be dropped.

• If you drop a simple materialized view that is the least recently refreshed materialized view of a master table, Oracle automatically purges from the master table's materialized view log only the rows needed to refresh the dropped materialized view.

• If you drop a master table, Oracle does not automatically drop materialized views based on the table. However, Oracle returns an error when it tries to refresh a materialized view based on a master table that has been dropped.

• If you drop a materialized view, any compiled requests that were rewritten to use the materialized view will be invalidated and recompiled automatically. If the materialized view was prebuilt on a table, the table is not dropped, but it can no longer be maintained by the materialized view refresh mechanism.

Examples

DROP MATERIALIZED VIEW Examples

The following statement drops the materialized view mv1 in the sample schema sh:

DROP MATERIALIZED VIEW mv1; 

The following statement drops the sales_by_month_by_state materialized view and the underlying table of the materialized view (unless the underlying table was registered in the CREATE MATERIALIZED VIEW statement with the ON PREBUILT TABLE clause):

DROP MATERIALIZED VIEW sales_by_month_by_state;