Summary: in this tutorial, you will learn how to use the Oracle
DROP VIEW statement to drop a view from the database.
Introduction to Oracle
DROP VIEW statement
To remove a view from the database, you use the following
DROP VIEW statement:
DROP VIEW schema_name.view_name [CASCADE CONSTRAINT];
First, you specify the name of schema that contains the view. If you skip the schema name, Oracle assumes that the view is in your own schema.
Second, you specify the name of the view to be dropped. If a view is referenced by other views, materialized views, or synonyms, Oracle will mark these objects
INVALID, but does not remove them.
Third, if a view has any constraint, you must specify the
CASCADE CONSTRAINT clause to drop all referential integrity constraints that refer to primary key and unique keys in the view. If you don’t do so, then the
DROP VIEW statement will fail in case such constraints exist.
DROP VIEW examples
The following statement creates a view named
salesman based on the
CREATE VIEW salesman AS SELECT * FROM employees WHERE job_title = 'Sales Representative';
The view returns only employees whose job titles are Sales Representative.
SELECT * FROM salesman;
The following statement creates another view named
salesman_contacts based on the
CREATE VIEW salesman_contacts AS SELECT first_name, last_name, email, phone FROM salesman;
salesman_contacts view returns the only name, email, and phone of the salesman:
SELECT * FROM salesman_contacts;
To drop the
salesman view, you use the following statement:
DROP VIEW salesman;
salesman_contacts view is dependent on the
salesman view, it became invalid when the
salesman view was dropped.
You can check the status of a view by querying data from the
user_objects view. Note that the object name must be in uppercase.
SELECT object_name, status FROM user_objects WHERE object_type = 'VIEW' AND object_name = 'SALESMAN_CONTACTS';
As you can see, the status of the
salesman_contacts view is
To drop the salesman_contacts view, you use the following
DROP VIEW statement:
DROP VIEW salesman_contacts;
In this tutorial, you have learned how to use the Oracle
DROP VIEW statement to drop a view from a database.