If you are getting below ORA-23515 error while dropping a tablespace in Oracle database, then please follow below steps to solve it.
SQL> drop tablespace TDS_DATAFILE including contents and datafiles CASCADE CONSTRAINTS; drop tablespace TDS_DATAFILE including contents and datafiles CASCADE CONSTRAINTS * ERROR at line 1: ORA-23515: materialized views and/or their indices exist in the tablespace
Please follow below steps to resolve the issue:
1. Login as sysdba in DB machine.
SQL> sqlplus /nolog SQL> conn /as sysdba;
2. Type below commands after replacing the user_name with your oracle schema name.
select object_name,object_type from dba_objects where owner='user_name' and object_type='MATERIALIZED VIEW';
SQL> select object_name,object_type from dba_objects where owner='TDS' and object_type='MATERIALIZED VIEW'; OBJECT_NAME OBJECT_TYPE ------------------- MT_TDS_R_RPT MATERIALIZED VIEW
3. Record the view name and login to oracle schema and drop the respective MATERIALIZED VIEW by typing below command
SQL> drop materialized view MT_TDS_R_RPT; Materialized view dropped.
4. Now login as sysdba and drop the tablespaces.
Hope this helps.