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

Another way to rebuild the table is to do a 'Create Table As'. It's a little more work, but, would do the job you need to do. Here's an example

create table new_table as
  select *
    from old_table;

BTW, you can specify all the necessary table parameters needed in the create statement. Also, be sure that you create all the DDL necessary to fully restore the table back to its original structure, i.e. all triggers, grants, foreign keys pointing to and from the table, etc ...... Also, if you do NOLOGGING in the create, the create will go a little quicker, however, make sure you backup the database soon after.  Also, it'd probably be a good idea to rename the current table at just before the create so that no one may use the table while it's being rebuilt.

HTH,
Pete's

All disclaimers apply, follow the above at your own risk.

"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 - 08:51:41 CDT