Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities
Droping a Tablespace - Printable Version

+- Oracle Forum - The Knowledge Center for Oracle Professionals - Looking Beyond the Possibilities (http://www.oraerp.com)
+-- Forum: Platform as a Service (PaaS) (http://www.oraerp.com/forum-40.html)
+--- Forum: Oracle Technology (http://www.oraerp.com/forum-16.html)
+---- Forum: Oracle Database (http://www.oraerp.com/forum-18.html)
+---- Thread: Droping a Tablespace (/thread-29.html)



Droping a Tablespace - admin - 10-20-2010

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.



RE: Droping a Tablespace - Kashif Manzoor - 10-21-2010

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