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.