URGENT HELP ME!

[tracy]

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

[Romeo Olympia]

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.

tr********@yahoo.com.hk (tracy) wrote in message news:<5c**************************@posting.google. com>...

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

[LC]

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

tr********@yahoo.com.hk (tracy) wrote in message news:<5c**************************@posting.google. com>...

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