Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities

Dear all,

can you please tell me how to drop a tablespace which has materialized views.I tried the following
drop tablespace portal including contents and datafiles;

but i get the following error message

SQL> drop tablespace portal including contents and datafiles;
drop tablespace portal including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace


there are about 200 indices in the tablespace dropping the all one by one is very cumbersome.Is there any other way i can do this and drop the tablespace.

SQL> alter tablespace <tbname> offline;

Tablespace altered

SQL> drop tablespace <tbname> including contents;

drop tablespace BLUH including contents

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

Try the below script...

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 = ‘tbname’);

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 <tbname> including contents

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

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

Tablespace dropped