RE: dbms package? - analyze table validate structure
Date: Wed, 31 Dec 2008 15:56:45 -0500
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 184.108.40.206 since this is 220.127.116.11, but a major update to 10g / 11g is a long time off for this project.
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
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
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.
Date: Wed, 31 Dec 2008 10:01:48 -0800
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.
Have you investigated why indexes are being corrupted?
I would think that to be a rather unusual circumstance.
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.