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

While dropping a tablespace it fails with error ORA-29857 like below.

SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution:

Drop the domain indexes on the tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

SQL> select index_name from dba_indexes where index_type = 'DOMAIN' and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects.

select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you don’t need these users you can try to delete users

SQL> drop user HR cascade;
User dropped.

SQL> drop user OE cascade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop user ix cascade;
User dropped.

after that we can delete tablespace

SQL> drop tablespace example including contents and datafiles;
About these ads
This entry was posted in Oracle and tagged , , . Bookmark the permalink.

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

  1. Asking questions are actually fastidious thing if you are not
    understanding something entirely, but this article gives nice understanding yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s