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

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.
Sample commands:

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

Sample Output:

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

Sample Output:

SQL> drop materialized view MT_TDS_R_RPT; 

Materialized view dropped.

4. Now login as sysdba and drop the tablespaces.

Hope this helps.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.