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


I tried to drop the tools tablespace and got this error.

SQL> DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES

******************************************************

ERROR at line 1:

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

Solution:

Step :1 Create a new tablespace with expected size

Step:2  Move object from original tbs to the second one

alter table <table_name> move <tablespace_name>

alter index <index_name> rebuild <tablespace_name>

alter table <table_name> move lob (<column_name>) store as (tablespace <tablespace_name>)

The lobindex will follow by itself.

Step:3  Be sure the original tablespace is now empty

Step:4  Drop the original tablespace

Step:5  Rename the new tablespace as the old one

alter tablespace <new_tablespace_name> rename to <old_tablespace_name>

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

%d bloggers like this: