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