+- 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