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

Error:

Drop tablespace failed with below error :

 SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

Solution:

We may use the following queries to find out which materialized views and/or which indexes of materialized view container tables are on the tablespace you intend to drop.

Once these objects are found you can move the tables, rebuild indeces in other tablespace or drop them as required.

 select s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  from sys.obj$ o, sys.tab$ t, sys.user$ u, sys.snap$ s , sys.ts$ p
  where t.ts#=p.ts# and p.name='<tablespace_name>'
  and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname
  order by 1,2;

select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace
  from dba_objects o,
  (select i.obj#,s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  from sys.obj$ o, sys.tab$ t, sys.ind$ i, sys.user$ u, sys.snap$ s,sys.ts$ p where i.ts#=p.ts# and p.name='<tablespace_name>'
  and i.bo#=t.obj# and t.obj#=o.obj# and o.owner#=u.user# and u.name=s.sowner and o.name=s.tname ) sq
  where sq.obj#=o.object_id
  order by 1,2,3;




If you like please follow and comment