Thursday, July 11, 2013

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

Was trying to drop a tablespace that was not needed anymore and I receive this error:

drop tablespace ts1 including contents and datafiles cascade constraints;

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

Solution:
Run the following query to get the materialized views that it is talking about and drop them first and then you can drop the tablespace without any issues.

select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
  from dba_registered_snapshots
 where name in (select table_name from dba_tables where tablespace_name = 'TS1');


1 comment: