RE: dbms package? - analyze table validate structure

As far as I can tell nothing is rebuilding the indexes, but I have not looked closely into that aspect of the problem. You bring up a valid point- doing an "alter index rebuild" does not solve the problem, it just moves the bad keys / pointers around. The index must be dropped and recreated. Other than sessions throwing errors, the real way I found this was ORA-0600 errors with 12700 as the first return code, which indicates that the index is pointing to invalid rows. This is also accompanied by trace files in UDUMP. Fortunately the ORA-0600 lookup tool documents this problem and indirectly lead me to drop / recreate indexes as a solution. The problem can be confirmed with a "analyze table validate structure" statement. I have recommended that we patch up to 9.2.0.8 since this is 9.2.0.7, but a major update to 10g / 11g is a long time off for this project.  

Regards, Jon  

From: M Rafiq [mailto:rafiq9857_at_hotmail.com] Sent: Wednesday, December 31, 2008 3:42 PM To: jkstill_at_gmail.com; Crisler, Jon
Cc: oracle list
Subject: RE: dbms package? - analyze table validate structure  

Jon,  

The issue of having corrupted indexes is quite frustrating. Jared raised a valid point.  

Is there any job keep running rebuidling indexes with parallel clause and those indexes have multiple keys? or someone is rebuiding them manually on regular basis? The issue is always using parallel caluse on a index with multiple keys. Single key indexses has no issues.  

Oracle bringing this bug in various versions resulting corrupted indexes. The solution is to drop all such indexes and recreate them (not rebuiding again).
You might be getting error messages in alert_sid.log file for such corruption when such index is being accessed.  

If this is true then you might look for a patch to fix it.  

Analyze table (not index) with validate structure is the most effective way to track such corruption.  

Regards
Rafiq      

Date: Wed, 31 Dec 2008 10:01:48 -0800
From: jkstill_at_gmail.com
To: Jon.Crisler_at_usi.com
Subject: Re: dbms package? - analyze table validate structure CC: oracle-l_at_freelists.org

On Wed, Dec 31, 2008 at 8:37 AM, Crisler, Jon <Jon.Crisler_at_usi.com> wrote:

        Does anybody know if there is an equivalent Oracle provided package like

        DBMS_STATS that does the same thing as "analyze table (name) validate

        structure" ? I have a system that seems to encounter corrupted indexes

        now and then, am I want to implement a preventative process via dbms_job

        to check all the tables and indexes in a particular schema.

Hi Jon,

Have you investigated why indexes are being corrupted?

I would think that to be a rather unusual circumstance.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist  

It's the same Hotmail(r). If by "same" you mean up to 70% faster. Get your account now.
<http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_bro ad1_122008>

--
http://www.freelists.org/webpage/oracle-l