Index-Organized Tables (suck ???)

Help please... we just took over database responsibilities for a newer version of Oracle databases (8.0.6 to be specific). We are used to 7.3.2... which is pretty pathetic itself.. old,old,old...

In 8.0.6 database, we have a couple of index segments that repeately reach maxextents. Simple enough, I guess... We just alter index and increase maxextents which works for a couple of weeks.

Anyways, yesterday I decided to 'analyze index <name> validate structure;' Queried index_stats and found out there are 81% deleted entries..

In 7.3.2, we would just 'alter index <name> rebuild tablespace <ts_name>; Done..

Well, it won't work in these particular 8.0.6 tables because they are index-organized tables.

After much research the last two days, I found out in 9i databases (8i too, I think)... you can use 'alter table <iot_table name> move tablespace <ts_name>; to rebuild the IOT table.

But that doesn't seem to work on the 8.0.6 database. Looking through the white Oracle reference guides, it looks like I might be able to 'alter table' with a partitioning_clause but I can't seem to make that work either.

Any suggestions? Please help me, I am a beaten down little DBA that is tired of thumbing through all these darn books...

thanks!
John Received on Thu May 16 2002 - 23:33:56 CDT