Use this statement to remove a materialized view from the database.

Syntax

DROP MATERIALIZED VIEW [ IF EXISTS ] [ owner.]materialized-view-name 

Remarks

All data in the table is automatically deleted as part of the dropping process. All indexes and keys for the materialized view are dropped as well.

Use the IF EXISTS clause if you do not want an error returned when the DROP MATERIALIZED VIEW statement attempts to remove a materialized view that does not exist.

You cannot execute a DROP MATERIALIZED VIEW statement on an object that is currently being used by another connection.

Executing a DROP MATERIALIZED VIEW statement changes the status of all dependent regular views to INVALID. To determine view dependencies before dropping a materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.

Permissions

Any user who owns the object, or has DBA authority, can execute the DROP MATERIALIZED VIEW statement.

Side effects

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Closes all cursors for the current connection.

When a view is dropped, all procedures and triggers are unloaded from memory, so that any procedure or trigger that references the view reflects the fact that the view does not exist. The unloading and loading of procedures and triggers can affect performance if you are regularly dropping and creating views.

See also

• CREATE MATERIALIZED VIEW statement
• ALTER MATERIALIZED VIEW statement
• REFRESH MATERIALIZED VIEW statement
• Materialized view statuses and properties

Standards and compatibility

Example

Drop MyMaterializedView from the database. If the materialized view does not exist, an error is returned.

DROP MATERIALIZED VIEW MyMaterializedView;