[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Index Corrupted

Posted on 2011-05-13
13
Medium Priority
?
307 Views
Last Modified: 2012-06-27
Hi In our live database   one table INDEX is corrupted,
the error iam getting is
Exception occured :
Message :ORA-08102: index key not found, obj# 29426
but the problem here the Index is done on Primary column
UNIQUE INDEX.

I have done using the sqldeveloper Rebuild the Index , But it doesnt work.
The  table is not allowing me to delete the records.when ever i try  to delete the records
iam getting problem.
Please help me to resolve the issue.
Please can one breif me why iam getting these  error and the solutions steps clearly which i can  follow.

thanks
Raj
0
Comment
Question by:nrajasekhar7
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35753313
What is the error message you got from sqldevoper?

How did you try to rebuild the index? Something like:

alter index [your index_name] rebuild online;

0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35753493
ALTER INDEX ALL ON table_name REBUILD WITH(ONLINE = ON)
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35753741
Ok, what is the error you get in sqldeveloper?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35754389
How large is the table?  How many foreign keys are there?

If there aren't many foreign keys and it's not huge, create a new table as select from the old one. Drop the original, recreate it as it was, insert the rows back in.

Or try dropping and recreating the primary key.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 1000 total points
ID: 35756476
I was taught that for corrupted indexes, don't rebuild them as Oracle uses the old index for building the new one.

Drop / create the index to force Oracle to scan the table.

I cannot find docs at the moment to support this, but I'm pretty sure this is the way.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35757158
@mrjoltcola - found a similar issue at orafaq.com, advice there is the same: drop and recreate the corrupt index.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35757213
@gewinjansen: Right. And in my experience, when I've had corrupt indexes, rebuilding would actually fail sometimes, this is because of Oracle trying to use the old index as the source of its data.
0
 

Author Comment

by:nrajasekhar7
ID: 35791807
Partially used
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 35792238
Hi nrajasekhar7, what do you mean by 'Partially used'? Did you drop and recreate the index?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36130010
I suggest split:  
accept:  http:#a35754389  since it first mentioned drop/recreate.
assist: http:#a35756476  since it confirmed it.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question