Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there’s another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.

It is tedious and cumbersome, because those dependent objects can have further dependencies, which also may have other dependencies and so on. I created utility functions which can help in such situations.

The usage is very simple – you just have to call:

You have to pass two arguments: the name of the schema and the name of the object in that schema. This object can be a table, a view or a materialized view. The function will drop all views and materialized views dependent on p_schema_name.p_object_name and save DDL which restores them in a helper table.

When you want to restore those dropped objects (for example when you are done modifying p_schema_name.p_object_name), you just need to make another simple call:

and the dropped objects will be recreated.

These functions take care of:

• dependencies hierarchy
• proper order of dropping and creating views/materialized views across hierarchy
• restoring comments and grants on views/materialized views

Click here for a working sqlfiddle example or check the gist below for a complete source code.

This post was published originally at my blog, which you can find here http://mwenus.blogspot.com.