indexes and/or secondary objects exist in the tablespace
Oracle Error Tips by Burleson Consulting
Oracle docs note this about ORA-29857:
ORA-29857: domain indexes
and/or secondary objects exist in the tablespace
attempt was made to drop a tablespace which contains
secondary objects and/or domain indexes.
Action: Drop the
domain indexes in his tablespace. Also, find the domain
indexes which created secondary objects in this tablespace
and drop them. Then try dropping the tablespace.
Many times, users become confronted with error
ORA-29857 when trying to identify tablespaces. For example,
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.
To identify the indexes of the domain, try
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