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

While dropping a tablespace it fails with error ORA-29857 like below.

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

The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution:

Drop the domain indexes on the tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

SQL> select index_name from dba_indexes where index_type = 'DOMAIN' 
and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects.

select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you don't need these users you can try to delete users

SQL> drop user HR cascade;
User dropped.

SQL> drop user OE cascade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop user ix cascade;
User dropped.

after that we can delete tablespace

SQL> drop tablespace example including contents and datafiles;