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

转自 http://www.dba-oracle.com/sf_ora_29857_domain_indexes_and_or_secondary_objects_exist_in_the_tablespace_bc1.htm

Oracle docs note this about ORA-29857:

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

Cause: An 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, 

 

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

---

You may also want to check out this information page about Oracle domain indexes:

http://www.dba-oracle.com/t_ault_89_indextype_udt_opstatus.htm

 

Monitoring Domain Indexes

Also introduced in Oracle8i was the concept of extensible indexing, also known as domain indexing. A domain index is usually used in cartridge development. In fact, a domain index is called so because it is used only within the domain of its parent cartridge. A domain index extends the basic types of hash, bitmapped, and B-tree indexes by allowing the developer to create his or her own index methods and apply them to a specific type of data set.       

An example of the use of domain indexing would be the use of R-tree indexes for spatial data. A domain index is based on the concept of an INDEXTYPE, which, like a User Defined Type (UDT), is created and maintained by the user. In order to use a domain index, a data cartridge that implements its structures, methods, and types must be created. 

Note

The domain indexes are indicated by a non-NULL value in the DOMIDX_STATUS and DOMIDX_OPSTATUS columns in the DBA_INDEXES view. I assume a join can be based on either the INDEX_NAME and INDEXTYPE_NAME in the DBA_INDEXES and DBA_INDEXTYPES table supplemented by the OWNER columns in each or the INDEX_TYPE and INDEXTYPE_NAME columns (even though they don’t match in size). I say “assume,” because this topic is beyond the scope of this book, so I leave it to the DBA who is involved in a cartridge development effort to actually create the reports required based on the supplied join data.