ORA-23515: materialized views and/or their indices exist in the tablespace

출처 http://ora34913.wordpress.com/2008/09/04/ora-23515-and-ora-02449-on-drop-tablespace/

 

 

 

SQL> alter tablespace BLUH offline;

Tablespace altered

SQL> drop tablespace BLUH including contents;

drop tablespace BLUH including contents

ORA-23515: materialized views and/or their indices exist in the tablespace

SQL> select ‘drop materialized view ‘||owner||’.'||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in  (select table_name from dba_tables where tablespace_name = ‘BLUH’);

drop materialized view ABC.CB_BLUH_DM_DATE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_ROLE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_DM_INTER_VW PRESERVE TABLE;
…

[After MVs were dropped]

SQL> drop tablespace BLUH including contents

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

SQL> drop tablespace BLUH including contents and datafiles CASCADE CONSTRAINTS;

Tablespace dropped