470,594 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

URGENT HELP ME!

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
Jul 19 '05 #1
2 5424
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

Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Stefan Bauer | last post: by
28 posts views Thread by Tamir Khason | last post: by
16 posts views Thread by | last post: by
7 posts views Thread by zeyais | last post: by
33 posts views Thread by dembla | last post: by
8 posts views Thread by ginnisharma1 | last post: by
3 posts views Thread by N. Spiker | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.