Skip to content
Home » Oracle » How to Resolve ORA-23515: materialized views and/or their indices exist in the tablespace

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

ORA-23515

Tried to drop a tablespace from the database in order to reclaim some space, but it failed with ORA-23515.

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

ORA-29857 means that the tablespace you want to drop contains materialized views or indexes associate with materialized views, which should be handled before dropping the tablespace.

Solution

We should drop those materialized view and their indexes first. Here we compose dropping statements.

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 = 'EXAMPLE'
  2  union
  3  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 = 'EXAMPLE';

STMT
--------------------------------------------------------------------------------
drop index SH.FW_PSC_S_MV_CHAN_BIX;
drop index SH.FW_PSC_S_MV_PROMO_BIX;
drop index SH.FW_PSC_S_MV_SUBCAT_BIX;
drop index SH.FW_PSC_S_MV_WD_BIX;
drop materialized view SH.CAL_MONTH_SALES_MV;
drop materialized view SH.FWEEK_PSCAT_SALES_MV;

6 rows selected.

You should replace the tablespace name in the above with yours.

As you can see, I union two statements for composing the dropping statements in one time.

Then we should execute those statements.

SQL> drop index SH.FW_PSC_S_MV_CHAN_BIX;

Index dropped.

SQL> drop index SH.FW_PSC_S_MV_PROMO_BIX;

Index dropped.

SQL> drop index SH.FW_PSC_S_MV_SUBCAT_BIX;

Index dropped.

SQL> drop index SH.FW_PSC_S_MV_WD_BIX;

Index dropped.

SQL> drop materialized view SH.CAL_MONTH_SALES_MV;

Materialized view dropped.

SQL> drop materialized view SH.FWEEK_PSCAT_SALES_MV;

Materialized view dropped.

We can try to drop the tablespace now.

SQL> drop tablespace example including contents and datafiles cascade constraints;

Tablespace dropped.

Leave a Reply

Your email address will not be published. Required fields are marked *