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

When I was trying to drop a tablespace, I got then below error.
ORA-23515: materialized views and/or their indices exist in the tablespace
The Oracle documentation has given a clear information about the resolution steps we need to perform.
According to Oracle ORA-23515 means.

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

Cause
An attempt was made to drop a tablespace which contains materialized views and/or their indices.

Action
Drop the materialized views in this tablespace. Also, find indices belonging to materialized views in this tablespace and drop then. Then try dropping the tablespace.

Solution:

Check for any materialized views and indexes present in the tablespace, delete them and try again.

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

'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'
—————————————————————————————————-
drop materialized view SCHEME_MGMT.EHF_PREAUTH_REPORT_MV PRESERVE TABLE;

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

'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'

—————————————————————————————————-

drop materialized view SCHEME_MGMT.EHF_PREAUTH_REPORT_MV PRESERVE TABLE;

Drop this materialized view then continue dropping the tablespace.

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.