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

Thanks HJR... You're right. I kept getting hung up on the fact that it was an 'index' segment showing up as hitting maxextents when it was really the underlying IOT..(I know it is a table organized as an index)

CTAS is the fix action I took. I noticed that the Primary Key is generated by a sequence (through a trigger)... isn't this a bad candidate for an IOT???? Thanks for your advice.

John

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:ac48qf$1vr$1_at_lust.ihug.co.nz...
> 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 Tue May 21 2002 - 01:05:36 CDT