drop tables and materialized views

When dropping tables and mv's, you need to make sure that there are no foreign keys refrencing the primary keys or unique keys of the tables you are wanting to drop.

Otherwise you will get the following type of errors.


ORA-02449: unique/primary keys in table referenced by foreign keys


With tables, you can use the cascade constraints option, but there appears to be no such option for materialized views.

To drop such mvs, you need to drop the relevent FKs, disabling the FK does nothing.

Use the following query to drop these constraints.

SQL> select 'alter table ' || owner || '.' || table_name || ' drop constraint ' || constraint_name || ';' from dba_constraints where r_constraint_name = [constraint name]