Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Oracle doesn't have the equivalent of schemabinding in SQL Server –  Eric Higgins Mar 14 '13 at 18:35
add comment

1 Answer 1

up vote 6 down vote accepted

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;
share|improve this answer
    
@LordScree - Yasir is correct, there are many ways you could break the customer's views with changes, but no way for their views to break your ability to change the tables as you see fit. –  Leigh Riffel Mar 15 '13 at 12:25
    
There's also a useful section in the Database 2 Day DBA, Validating (Compiling) Invalid Schema Objects –  Yasir Arsanukaev Mar 15 '13 at 14:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.