Re: unsed index

I Don't really understand the question. You are moving a table from one tablespace to somewhere else. That means that all your rows which used to be in 'File 4, Block 67' will now probably be sitting in 'File 8, Block 99'.

Since File and Block numbers are half of the Rowid, this means that a move operation will completely kack up every single rowid for that table's data. Now the rowid is not actually stored anywhere in the table, so that's not a problem. But it *is* stored in the leaf nodes of every single index created on your table. So moving your table means invalidating all the entries in all the leaf nodes of all the indexes on that table.

Short answer: you have to rebuild them all. Otherwise, you'll get an 'index is in an useable state' error.

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------

<rpereira_at_my-deja.com> wrote in message news:8s29dn$kja$1_at_nnrp1.deja.com...


> Hi Gurus,

>

> Is there anyone who knows why when I issue the following command to un-

> fragment the table all indexes of the table are unsed?

>

> alter table table_name move tablespace new_or_old_tablespace_name

> storage (initial 1M next 1M pctincrease 0)

>

> Look forward hearing from you.

>

> Thanks

>

>

>

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.