If you need to modify the SQL query on which a view is based, then either drop and re-create the view or use the CREATE or REPLACE syntax, as in the previous examples.

For instance, say you add a REGION column to the SALES table:

SQL> alter table sales add (region varchar2(30)); SQL> create or replace view sales_rockies as select sales_id, amnt, state, region from sales where state in (‘CO’, ‘UT’, ‘WY’, ‘ID’, ‘AZ’) with read only;

The advantage of using the CREATE OR REPLACE method is that you do not have to reestablish access to the view for users with previously granted permissions.

Also, you don’t have to re-create the view if you do not want to include the new column in the view.

However, if you remove a column that the view is using, the view will compile with errors, and you will have to re-create the view without the column.

Renaming a view is also possible with the RENAME statement.

SQL> rename sales_rockies to sales_rockies_old;

You should see this message:

Table renamed.

It would make more sense if it said “View renamed,” but the message in this case does not exactly match the operation.

Dropping a view makes sense if you are no longer using it.

SQL> drop view sales_rockies_old;

Keep in mind that when you drop a view, any dependent views, materialized views, and synonyms become invalid. Additionally, any grants associated with the dropped view are also removed.