• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2587
  • Last Modified:

corrupt index...

Hi Expert, I have a corrupt index and so can not get data from the table.
Can I rebuild this index with the normal rebuild comman or lets say what is the best way to rebuild this index?
SQL> alter index CITCO.XPGL rebuild;
alter index CITCO.XPGL rebuild*ERROR at line 1:ORA-08103: object no longer exists
0
sharscho
Asked:
sharscho
  • 6
  • 4
  • 4
  • +2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
best is to drop the index and recreate it completely...
0
 
sharschoAuthor Commented:
Ok I will do so. Thanks but I have another question regarding this issue, How do I get to know if more indexes are corrupt?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Ok I will do so. Thanks but I have another question regarding this issue, How do I get to know if more indexes are
corrupt?

check if there are any rows in the view DBA_INDEXES that have a status of invalid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mohammadzahidCommented:
Please take a look at the urls pasted below. I have never fixed such problem but I think these site will help answer your index corruption question.

http://www.quest-pipelines.com/newsletter-v4/0103_C.htm

http://www.oracle-base.com/articles/8i/DetectAndCorrectCorruption.php

0
 
Harish_RajaniCommented:
Hi sharscho,

How did you find out in first place that index is corrupt?
The error you are getting is interesting, does this index existed in first place.

If you haven't dropped and recreated the index in question , check in dba_indexes or dba_ind_partitions to see if the index exists in first place.

Rgds,
HR
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume that the table has been modified after the index got created, thus invalidating the index...
0
 
actonwangCommented:
>>ORA-08103: object no longer exists
      it might be that index built on table which your current user doesn't have permission to acess to ...
0
 
sharschoAuthor Commented:
How do you mean modify the table? I just dropped the index and create it with the same script it was created in the first place. So it is much complicated than that then? OOps...

I found out about the error when trying to select data from the table in question and I was getting that the row did not exist. ORA-08005: specified row does not exist
This same selection is done very month and last month it was OK.

Please let me know what I need to do for the table to which this index belongs. I did the select and got all the data I needed after that I recreated the index and so I believe that the index was a problem indeed.
0
 
sharschoAuthor Commented:
it is not a permission problem because I could see all the index info in TOAD under this schema name...
0
 
actonwangCommented:
>>alter index CITCO.XPGL rebuild*ERROR at line 1:ORA-08103: object no longer exists
     make sure you have access to tables in schema CITCO.

     I don't think it is "corrupt" problem.
   
0
 
Harish_RajaniCommented:
Try out Acton's suggestion, though you can SEE the index, you might not have the privelege to alter it.

Is this a partitioned index by any chance?


Rgds.
HR
0
 
sharschoAuthor Commented:
I was logged in as the schema owner when I tried to test these commands. I first did this query:
select max(invoice_no) from citco.xpgl
ERROR:
ORA-08005: specified row does not exist
0
 
actonwangCommented:
hmm...

can you run other simple query?
as

select * from xpgl where rownum < 2;

 how about "desc xpgl;""?
0
 
Harish_RajaniCommented:
citco.xpgl  is this table or is it a index?

Rgds,
HR


0
 
actonwangCommented:
ha..
0
 
sharschoAuthor Commented:
Hmmm if I could drop the index as schema owne, doesn't that mean I can alter it? It was a unique index.
Is there a problem with the table now that I have dropped and recreated the index?
0
 
Harish_RajaniCommented:
Dropping and recreating of index does not cause any problem to the underlying table.




.
0
 
sharschoAuthor Commented:
OK Thank you all for your help and suggestions.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now