ORA-23515: Materialized Views and/or Their Indices Exist in the Tablespace

While dropping any Tablespace, you might get the errror 

ORA-23515: Materialized Views and/or Their Indices Exist in the Tablespace

This is due to the fact that the tablespace contains the indexes or Materialized views

You may use the following queries to find out which materialized views and/or which indeces 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.

 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;

 

An example output listing mviews and their owner where the container table of the mview is on the tablespace <tablespace_name>
 

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

MV_OWNER                       MV_NAME                        TABLESPACE
------------------------------ ------------------------------ ------------------------------
<user_name>                    TEST_MV                        <tablespace_name>
<user_name>                    CUST_MV                        <tablespace_name>

An example output listing indeces of mviews container tables, where the index is on tablespace <tablespace_name>

SQL> select o.owner, o.object_name index_name, o.object_type, sq.mv_owner,sq.mv_name,sq.tablespace
  2    from dba_objects o,
  3    (select i.obj#,s.sowner MV_OWNER, s.tname MV_NAME, p.name TABLESPACE
  4    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>'
  5    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
  6    where sq.obj#=o.object_id
  7    order by 1,2,3;

OWNER      INDEX_NAME                     OBJECT_TYPE         MV_OWNER                       MV_NAME                         TABLESPACE
---------- ------------------------------ ------------------- ------------------------------ ------------------------------ ------------------------------
<user_name>  I_SNAP$_TEST_MV                INDEX              <user_name>                             TEST_MV                        <tablespace_name>
<user_name>  I_SNAP$_CUST_MV                INDEX              <user_name>                             CUST_MV                        <tablespace_name>

YOU MAY DELETE THE OBJECTS or MOVE Them to other tablespaces and then try the delete Tablespace command