Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

DROP MATERIALIZED VIEW

Purpose

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

When you drop a materialized view, Oracle Database does not place it in the recycle bin. Therefore, you cannot subsequently either purge or undrop the materialized view.

Note:

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

See Also:

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 the database uses to maintain the materialized view data.

See Also:

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

Syntax

drop_materialized_view::=

Description of drop_materialized_view.gif follows
Description of the illustration drop_materialized_view.gif

Semantics

schema

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

materialized_view

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

PRESERVE TABLE Clause

This clause lets you retain the materialized view container table and its contents after the materialized view object is dropped. The resulting table has the same name as the dropped materialized view.

Oracle Database removes all metadata associated with the materialized view. However, all indexes created on the container table automatically during creation of the materialized are preserved. Also, if the materialized view has any nested table columns, then the storage tables for those columns are preserved, along with their metadata.

Restriction on the PRESERVE TABLE Clause This clause is not valid for materialized views that have been imported from releases earlier than Oracle9i, when these objects were called "snapshots".

Examples

Dropping a Materialized View: Examples The following statement drops the materialized view emp_data in the sample schema hr:

DROP MATERIALIZED VIEW emp_data; 

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;