Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2010-01-09
Medium Priority
Last Modified: 2013-12-18

I am trying to drop a schema but am receiving  Oracle error ORA-29857:domain indexes and /or secondary objects exist in the tablespace.

I ran a select index_name, table_ownder, table_name, tablespace_name
from all_indexes where index_type = 'DOMAIN';

Now am I suppose to delete those indexes? I am not sure why it won't let me just drop the schema. Shouldn't it automatically drop everything within the talespace
including indexes and other objects? Also just need the command to delete or drop the indexes

Thank you.
Question by:Sainai
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26272506

Author Comment

ID: 26272617
I already looked at it. I dropped the associated indexes except the ones it won't allow me to due to primary/unique constraints.

Still getting same error: Oracle error ORA-29857:domain indexes and /or secondary objects exist in the tablespace
LVL 11

Accepted Solution

it-rex earned 2000 total points
ID: 26276189
use this dynamic SQL
which will generate the DDL for you

for the owner name use the schema you rae trying to drop

select 'drop index '||owner||'.'||index_name||';' from dba_indexes
where index_type='DOMAIN'
and owner='<SCHEMA_NAME>'

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question