Can a view affect ALTER TABLE commands?

Question

I provide a database system to a number of customers. The database is installed locally on Oracle.

We periodically upgrade the database structure (either adding new fields, renaming old fields, changing things around), as the product grows.

One of my customers wishes to add their own custom views to the database. They've assured me they will add the view to a different schema to the live schema. However, I am concerned that the inclusion of a view might affect future upgrade scripts.

If a view references a table that is to be altered, will the presence of the view prevent the table from being altered?

Answer 1

No, it shouldn't prevent the table from being altered. Though, if you drop the underlying table a view depends on, or alter/remove the columns from the table the view uses, the view can become invalid. You can check what views are invalid in your system with the following query:

sql> select name from dba_objects where object_type = 'VIEW' and status = 'INVALID';

You should fix the problems before the view can be made VALID. The view can be compiled manually:

sql> alter view foo compile;

Or you can allow the system automatically compile it when someone attempts to query the view. However manual compilation is the recommended way because it reduces the execution time of the first query. Usually views and other invalid objects (procedures, triggers etc.) are recompiled in bulk using a supplied script utlrp and this is a routine task for a DBA.

If you want to create the view but the base table doesn't yet exist, you can use the option FORCE:

sql> create force view foo as select * from moo;