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;
Post a Comment
Post a Comment