Re: Index-Organized Tables (suck ???)

Why are you comparing apples and pears?

It's pointless saying 'in Oracle 7 we used alter index rebuild' but we can't do this in 8.0 because these are IOTs! An IOT is a table, not an index, though it happens to be *structured* like an index. So you can't expect to use commands specific to an index when working with a table. (Validate structure is OK, because it's the b-tree-ness of the table you are validating).

It's also the case that IOTs were first introduced in 8.0, with many restrictions (of which the one that is bugging you is a classic), to the point where I wouldn't have touched them with a barge pole.

With all that in mind, what would you have done in Oracle 7 to reorganise a table? You could do a CTAS. You could try an export, truncate, import. But that's about it.

Regards
HJR "JohnB" <jcj_becker_at_yahoo.com> wrote in message news:UK%E8.14445$DF2.2703107_at_twister.socal.rr.com...
> 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 Fri May 17 2002 - 19:54:32 CDT