How to DROP VIEW in Oracle?

 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)

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)

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)

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.