Rebuild index

I have one  huge table which conists of  million rows , i that tabe i have 4 columns index is done,
i always when i run the archive script , its failling on the object id  for each column index giving  me the problem saying the index corrupted,we are using oracle 9i.

Please help me to resolve these.
Please suggest me the steps which i should follow.

Thanks in advanca
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Just drop the index and re-create it. If the index is the only segment containing corrupted blocks you'll be fine. Rebuilding won't help because it uses the existing index data to build the new one, so you will hit the corrupted blocks and get an error.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

If you are looking for the syntax to rebuild you index :

alter index <your_idx_name> rebuild;

I'd strongly suggest you to check also why your index gets corrupted. Rebuilding it is not a guarantee it won't become corrupted again....


Rebuilding mecanism builds a new index from the table data, then drops the former index. In other words does exactly what it should. No need to 'drop' the index and to 'create' it again.


Still you must check where's the corruption originating from. If it is your disks that are failing, you would rebuild somewhere else your indexes, for example....

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
I should also throw out an online rebuild of an index so DML can continue on the table and existing queries can take advantage of the old one:

alter index index_name rebuild online;
@Piloute: Quoted from 9i's docs:

"When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build."

"The REBUILD statement uses the existing index as the basis for the new one."

"Usually, ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks."

If you want to learn when Oracle does a full table scan or a fast full scan on the existing index from someone who actually took the time to research it (using the same 9i as the author), please go to this page and find this string "When I rebuild an index, I see Oracle doing a sort". To save you the time: ONLY if he uses the ONLINE clause does Oracle appear to start from the table in a consistent fashion. However, even this is not documented (and not what some answers are saying anyway).

He can try using rebuild ONLINE, it does appear to use a FTS, but he CANNOT in ANY WAY use just "rebuild", because it DOES start from the existing index more often than not. Remember we're dealing with corruption here.

The bottom line is: IMHO, to be on the safe side, he'd better DROP and CREATE the index.

Thanks for the quote. Haven't read the docs since long time ;)  

When you say you'll save me the time, I feel like you're angry at me.
1) No need to. Really thought a rebuild will use table data under any circumtances.
2) Will take the time to carefully read the demo of the guy and will check it myself. Seems interesting.

@Piloute: Sorry for the wording, no ill intentions... I'm just too used to people not reading anything else but the posts :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.