Hi,
I need help urgently. Yesterday i move a tables and indexes to
different tablespace using these command:
alter table my_table move tablespace xyz;
alter index my_index rebuild tablespace xyz;

now my db got this error:
ORA-01502 index 'string.string' or partition of such index is in
unusable state
for idx_cesdetail_ces_main_item

User found out a lot of such indexes. why? and how to fix it.

regards,
tracy

Maybe you were not able to rebuild all indexes associated with that
table you moved yesterday. Anyhow, suggest you just find all the
UNUSABLE indexes and rebuild them.

select table_name, index_name
from dba_indexes
where status = 'UNUSABLE';

table_name here is most likely the table you moved. Then for each row
you find there,

alter index index_name rebuild; -- can add nologging clause for faster
rebuild.

The above suggestions are assuming that you're not dealing with
partitioned indexes/tables. If you are, holler or better yet read the
Administration Guide (http://tahiti.oracle.com). Once you're done
putting out this fire, do visit that site.

HTH.

tracykim10 (AT) yahoo (DOT) com.hk (tracy) wrote in message news:<5c91cce9.0406221852.3ec8ca35 (AT) posting (DOT) google.com>...

hi,
u have 2 options: drop and recreate the index or rebuild the index
using steps below

1. check which indexes status in ('INVALID' OR 'UNUSABAL') FORM
DBA_INDEXES, USER_INDEXS
2. rebuild it using these command:
alter index indexname rebuild tablespace tablespacename;

good luck

tracykim10 (AT) yahoo (DOT) com.hk (tracy) wrote in message news:<5c91cce9.0406221852.3ec8ca35 (AT) posting (DOT) google.com>...