I have just found out that users have created tables on the system tablespace. If i use the ALTER TABLE xxx MOVE TABLESPACE xxx command to move these tables to a diferent tablespace, what are the implications of the constraints and indexes on the respective tables?? Do i have to disable constraints and rebuild indexes??..The indexes are situated in their default tablespace.
Originally posted by shailendra Well from this comand your tables will get transfered but the index won't get transfered so you have to rebuild all the Indexes.
This is not exactly the reason why indexes get 'UNUSABLE';
The reason is that in the index-leafs where the rowid points to the rows in the table-block get invalid, because the table-block has moved.
If you would move the index-blocks too the rowid would still be invalid;
But if there would be an additional clause : INCLUDING INDEXES STORAGE ...