Oracle DROP VIEW

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];
Code language: SQL (Structured Query Language) (sql)

1) schema_name

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.

2) view_name

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.

3) CASCADE CONSTRAINT

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.

Oracle DROP VIEW examples

The following statement creates a view named salesman based on the employees table:

CREATE VIEW salesman AS SELECT * FROM employees WHERE job_title = 'Sales Representative';
Code language: SQL (Structured Query Language) (sql)

The view returns only employees whose job titles are Sales Representative.

SELECT * FROM salesman;
Code language: SQL (Structured Query Language) (sql)
Oracle DROP VIEW - salesman view

The following statement creates another view named salesman_contacts based on the salesman view:

CREATE VIEW salesman_contacts AS SELECT first_name, last_name, email, phone FROM salesman;
Code language: SQL (Structured Query Language) (sql)

The salesman_contacts view returns the only name, email, and phone of the salesman:

SELECT * FROM salesman_contacts;
Code language: SQL (Structured Query Language) (sql)
Oracle DROP VIEW - salesman_contacts view

To drop the salesman view, you use the following statement:

DROP VIEW salesman;
Code language: SQL (Structured Query Language) (sql)

Because the 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';
Code language: SQL (Structured Query Language) (sql)
Oracle DROP VIEW - Invalid View

As you can see, the status of the salesman_contacts view is INVALID.

To drop the salesman_contacts view, you use the following DROP VIEW statement:

DROP VIEW salesman_contacts;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle DROP VIEW statement to drop a view from a database.

Was this tutorial helpful?