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

Problem Description
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

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

Solution of the Problem
Drop the domain indexes on the tablespace which you are going to delete. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

You can find whether tablespace EXAMPLE contains any domain index or not by running query,
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. However if you want to drop a tablepsace and if you think the user's objects in the tablespace is no longer needed you can try deleting all objects in that tablespace before dropping tablespace. You can also look for the list of users contain objects inside specified tablespace.

You can check it by,

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

If you think you no longer need these user you can try to delete users.

SQL> drop user HR casecade;
User dropped.

SQL> drop user OE casecade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop tablespace example including contents;
drop tablespace example including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop user ix cascade;
User dropped.

SQL> drop tablespace example including contents;