The DROP VIEW statement is used to remove a view from the database.




Dropping a view doesn’t erase user data from the database, unlike DROP TABLE. Remember, a view is a definition representing selected columns and/or rows from one or more underlying tables or views. So, you can drop a view (delete the definition) without affecting data in the database itself.

Any image of the view being dropped is also removed from RAM.

If you specify RESTRICT and another view is based on the view you want to drop, the DROP VIEW command will fail. RESTRICT is the default behaviour.

CASCADE removes the view and all views that are dependent upon it.

Example 1: Dropping a View with No Dependent Views

Drop the view partsupp_1002:

DROP VIEW partsupp_1002

This is identical to:

DROP VIEW partsupp_1002 RESTRICT

Example 2: Dropping Dependent Views

A view, US_ORDERS_PLACED, which is based on the CUSTOMER and ORDERTAB tables, is built. A second, dependent view, US_CUST_TOTALSPEND, based on US_ORDERS_PLACED is also built. Using the CASCADE option, drop both US_ORDERS_PLACED and US_CUST_TOTALSPEND:

DROP VIEW us_orders_placed CASCADE