Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

SQL Statements (continued), 10 of 30


DROP MATERIALIZED VIEW / SNAPSHOT

Syntax


Purpose

To remove an existing materialized view from the database.

The terms "snapshot" and "materialized view" are synonymous.

See Also:

 

Prerequisites

The materialized view must be in your own schema or you must have the DROP ANY MATERIALIZED VIEW (or DROP ANY SNAPSHOT) 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.

For information on these privileges, see "DROP TABLE", "DROP VIEW", and "DROP INDEX".

Keywords and Parameters

schema 

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

materialized view / snapshot 

is 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 detail table's materialized view log only the rows needed to refresh the dropped materialized view.

 

 

  • If you drop a detail 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 detail 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

The following statement drops the materialized view PARTS owned by the user HQ:

DROP SNAPSHOT hq.parts; 

The following statement drops the SALES_BY_MONTH 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;

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index