Mir Sayeed Hassan – Oracle Blog

Error occur while dropping tablespace as ORA-23515: materialized views and/or their indices exist in the tablespace

Drop the tablespace

SQL> drop tablespace MGMT_TABLESPACE including contents and datafiles;
drop tablespace MGMT_TABLESPACE including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

Execute the below query to identify the sysman materialized view.

SQL> select 'drop materialized view ' || m.owner || '.' || m.mview_name || ';' stmt from dba_mviews m, dba_tables t where t.table_name = m.mview_name and t.tablespace_name = 'MGMT_TABLESPACE' union select 'drop index ' || i.owner || '.' || i.index_name || ';' stmt from dba_indexes i, dba_mviews m, dba_tables t where i.table_name = t.table_name and i.table_name = m.mview_name and t.tablespace_name = 'MGMT_TABLESPACE';

STMT
--------------------------------------------------------------------------------
drop index SYSMAN.MGMT$ARU_PATCH_RECOM_MD_IDX1;
drop index SYSMAN.SYS_IL0000092604C00024$$;
drop index SYSMAN.SYS_IL0000092604C00027$$;
drop materialized view SYSMAN.MGMT$ARU_PATCH_RECOM_MD_E;

SQL> drop index SYSMAN.MGMT$ARU_PATCH_RECOM_MD_IDX1;
drop index SYSMAN.SYS_IL0000092604C00024$$;
drop index SYSMAN.SYS_IL0000092604C00027$$;
drop materialized view SYSMAN.MGMT$ARU_PATCH_RECOM_MD_E;

Index dropped.

SQL> drop index SYSMAN.SYS_IL0000092604C00024$$
*
ERROR at line 1:
ORA-22864: cannot ALTER or DROP LOB indexes

SQL> drop index SYSMAN.SYS_IL0000092604C00027$$
*
ERROR at line 1:
ORA-22864: cannot ALTER or DROP LOB indexes

SQL> Materialized view dropped.

Try to drop the tablespace, In-case if you still facing an error as shown below, fallow below step.

SQL> drop tablespace MGMT_TABLESPACE including contents and datafiles;
drop tablespace MGMT_TABLESPACE including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

To resolve the above error., you need to drop the tablespace by using the CASCADE CONSTRAINTS clause with DROP TABLESPACE as shown below.

SQL> drop tablespace MGMT_TABLESPACE including contents and datafiles cascade constraints;
Tablespace dropped.

=====Hence tested & verified inb our test env=====