unusable indexes

What does unusable index mean? Does that mean fragamented indexes?

it's not fragmented index. fragmented index still can be used. unusable index is the index not correctly corresponding to table, like some operation on partition table cause correspondin index unusable (index didn't changed, need rebuild).

One thing that comes to mind which can cause unusable indexes is when you move the table with an ALTER TABLE MOVE stmt.

So, how can I check for unusable indexes? Can any body write/show me a script that does
that?

query dba_indexes view. there is a column: status.

So to rebuild those unusable indexes, do you just
alter index index_name rebuild
just like you do when the index is fragmented? How do you solve the problem when you realize that your index is unsable?

thanks,
learn

yes, rebuild it. you can even rebuild index to another tablespace to move index.

after you query the the status from dba_indexes and the index is unusable, what does the status column display?
Does it say not valid or unusable?

that a good a question. Mine always shows valid so I guess it means your indexes are good. If they are unusable, I don't know how it will show

shows UNUSABLE

http://technet.oracle.com/docs/produ.../ch232.htm#877

Does anyone know the effect on Functions or Procedures that rely on the tables that have been "moved" with the

ALTER TABLE tablename MOVE tablespace

command?

After the MOVE command, if you have any objects (procedures, functions, packages, etc) that rely on the table that got MOVEd, the package, procedure, etc will become invalid. You can recompile it if you want with alter...compile
commands, but Oracle will compile them for you when you need to use them as long as there are no errors in the code.