Skip to content

How to Resolve ORA-29857: domain indexes and/or secondary objects exist in the tablespace

  • by

ORA-29857

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

SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

ORA-29857 means that the tablespace you want to drop contains one or more domain-related indexes associate with secondary objects, which should be handled before dropping the tablespace.

Solution

We should drop domain indexes first. Here we compose dropping statements.

SQL> select 'drop index ' || i.owner || '.' || i.index_name || ';' stmt from dba_indexes i, dba_tables t where i.table_name = t.table_name and i.index_type like '%DOMAIN%' and t.tablespace_name = 'EXAMPLE';

STMT
--------------------------------------------------------------------------------
drop index SH.SUP_TEXT_IDX;

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

Then we should execute those statements.

SQL> drop index SH.SUP_TEXT_IDX;

Index dropped.

We can try to drop the tablespace now.

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

Tablespace dropped.

It dropped. However, if you just want an empty database, you can drop the rest of tablespaces first, then go back to handle the problematic one.

Leave a Reply

Your email address will not be published.