index error...

[Fiona]

After successuly moving the table from one tablespace to another, it now gives error

ORA-01502: index 'GALAXY.SYS_C0028191' or partition of such index is in unusable state

Is it possible to move indexes as well? Or rebuild of indexes the only solution to thins?

Fiona

[marist89]

Yes, you must rebuild the indexes when you move a table:
alter index GALAXY.SYS_C0028191 rebuild;

You can also move it at the same time with:
alter index GALAXY.SYS_C0028191 rebuild tablespace new_tbs;

[Zaggy]

Rebuild is your only option, becuase of the invalid rowids from the move.

[Tianhua]

Can you rebuild the index or you have to drop it and recreate it? Because when you rebuild it, Oracle will use the old index to build the new one (does anyone know how?). My concern is that if the old index contains invalid rowid info, can you still use the index to rebuild a new one?

tianhua

[pando]

rebuild is like drop and recreate, in this case there is no difference between two because the index is unusable so in any case users cannot use it (I would guess recreate is a better way and save freespace)