Tablespace

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

ORA-23515

I got this error when I was trying to drop a tablespace with all options.

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

Let’s see the content of ORA-23515:

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.

Solutions

Actually, there’re dependencies need to be resolved. Let’s see which indexes and materialized views blocked our way. If there’s any, drop them.

SQL> column owner format a10;
SQL> select t.owner, t.table_name from dba_tables t inner join dba_indexes i on t.owner = i.table_owner and t.table_name = i.table_name where i.index_type= 'DOMAIN' and t.tablespace_name = 'EXAMPLE';

OWNER      TABLE_NAME
---------- ------------------------------
SH         SUPPLEMENTARY_DEMOGRAPHICS

SQL> drop table SH.SUPPLEMENTARY_DEMOGRAPHICS cascade constraints purge;

Table dropped.

SQL> select owner, object_name from dba_objects where object_type = 'MATERIALIZED VIEW';

OWNER      OBJECT_NAME
---------- ------------------------------
SYSMAN     MGMT_ECM_MD_ALL_TBL_COLUMNS
SH         CAL_MONTH_SALES_MV
SH         FWEEK_PSCAT_SALES_MV

SQL> drop materialized view SH.CAL_MONTH_SALES_MV;

Materialized view dropped.

SQL> drop materialized view SH.FWEEK_PSCAT_SALES_MV;

Materialized view dropped.

Let’s drop the tablespace again.

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

Tablespace dropped.

Another smooth way to drop a tablespace is to drop all related users (with cascade) first. Then drop the tablespace afterwards.

Leave a Reply

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