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

Many times, users become confronted with error ORA-29857 when trying to identify tablespaces.  For example, 

Question:

I want to detach, or delete,  a tablespace and datafile and got the error ORA-29857.  I  do not have a backup of my datafile, and am not in archive log mode.  I am trying to drop the tablespace with the objects owned by the files, but keep encountering ORA-29857. 

Answer:  To identify the indexes of the domain, try executing,

select index_name, table_owner, table_name, tablespace_name from user_indexes where index_type='DOMAIN';

The tablespace may not be specified, but you could at least see the potential indexes.  Remember that you should be connected with an index user status to run the above query.  If this is not possible, try ALL_INDEXES in place of USER_INDEXES,

select owner, index_name, table_owner, table_name, tablespace_name from all_indexes where index_type='DOMAIN';

As a last note, you should check and be sure that all indexes have clearly cut names, and monitor which tablespaces are created on which indexes.

• NOTE: In domain indexes, data can be stored in default databases, which is set at the time of the index creation by the user that created the index, and this can be confusing when trying to identify the indexes in a domain in hopes of resolving ORA-29857