Re: ORA-01499

Hi Hieraklion,

I'm not sure it is bug 1527982, because in MetaLink bug 1527982 is reported where a partitioned table has LOCAL BITMAP INDEXES and the partition key is updated such that the row moves to a different partition then the bitmap index is not updated correctly ..

But in my scenario, I don't have BITMAP INDEXES, it seems to be the ANALYZE command (with CASCADE clause ?) randomly causes the indexes to be corrupted. Some indexes were ANALYZED without error prior to execute:

 "ANALYZE TABLE mytable VALIDATE STRUCTURE CASCADE" (resulted with error ORA-01499 or ORA-01498)

Then indexes become corrupted after, and the trace file sometime reported block corruption or table/Index Cross Reference failure intermittently, dropping and recreating the table and indexes did not help.

MetaLink has reported bugs that resulted from ORA-01499 error, and I couldn't find any similar to this scenario.

Regards,

Vance.

Hieraklion <hieraklion_at_spray.fr> wrote in message news:<3C3C6370.4040000_at_spray.fr>...
> "Bonjour" from Paris
>
> Seems to be bug 1527982. First patch in 9i releases. Don't know the
> existence of backport.
>
> "Au revoir" from Paris.
> Hieraklion
>
> Vance Wu wrote:
>
> >Hi,
> >
> >Anybody out there experienced "ORA-01499 table/Index Cross Reference
> >Failure - see trace file." and knowing how to interpret the trace
> >file. Error occurred when I ANALYZE the table to validate the
> >structure of the table's indexes using CASCADE clause:
> >
> > ANALYZE TABLE mytable VALIDATE STRUCTURE CASCADE;
> >
> >Even dropping and recreating the indexes did not work, I also ANALYZE
> >individual indexes to verify the integrity of each data block in the
> >index and check for block corruption:
> >
> > ANALYZE INDEX myindex VALIDATE STRUCTURE;
> >
> >And intermittently found block corruption in some indexes, but it is
> >fixable by dropping and recreating the indexes. I kept
> >dropping/recreating the indexes until all indexes are fixed, but never
> >able to ANALYZE TABLE successfully using VALIDATE STRUCTURE CASCADE
> >clauses as mentioned above.
> >
> >Oracle MetaLink note# 1081343.6 said "Recreating the table via
> >export/import and then creating the indexes resolved the issue.", I
> >followed what they said without any luck.
> >
> >'mytable' contains five partitions, is the problem related to
> >partition issue? or is it just another Oracle bug? they blame on
> >hardware: "This was most likely caused by some type of hardware
> >problem as the problem failed to be reproduced in house." (see note)
> >which I don't believe. This problem happened on both
> >Oracle 8.1.6 (installed on Solaris 2.6), and Oracle 8.1.7 (installed
> >on Linux Redhat 6.2).
> >
> >Any help would be appreciated.
> >
> >Vance.
> >
Received on Wed Jan 09 2002 - 23:41:54 CST