?
Solved

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

Posted on 2010-01-09
3
Medium Priority
?
3,326 Views
Last Modified: 2013-12-18
Hi,

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
correctly.

Thank you.
0
Comment
Question by:Sainai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 26272506
0
 

Author Comment

by:Sainai
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
0
 
LVL 11

Accepted Solution

by:
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>'
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

770 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