On Wed, 26 May 1999, Goolalay Goolalay wrote:
> Hi Ari,
> My problem is I can't drop one particular table. Error message is
> ORA-00604: error occurred at recursive SQL level 1
> ORA-08102: index key not found, obj# 35, dba 83887328 (2)
>
> Not even describe the table. I asked Oracle support but they don't have any
> solution right now, the last thing I would do is to restructure everything.
> That particular table also cannot be exported.
>
> Another question is, can I export whole tables except a few in one line
> statement, exp73 ........ table=....
>
> Thanks in advance.
>
(NOTE: Additional advice from Allen Moore after my comments!)
The error messages that I see are:
08102, 00000, "index key not found, obj# %s, dba %s (%s)"
// *Cause: Internal error: possible inconsistency in index
// *Action: Send trace file to your customer support representative,along
// with information on reproducing the error
To find the object, issue:
SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 36;
If you can drop the object then you should be fine. Sometimes Oracle has
objects created with the name in lowercase:
SQL> CREATE TABLE "lower_tab" (COL1 VARCHAR2(100));
Table created.
SQL> DESC lower_tab;
Object does not exist.
SQL> SELECT * FROM lower_tab;
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM "lower_tab";
No rows selected.
SQL>
So, you can see that if you enclose the table (or index, etc.) in quotes
and put it in lower-case, then the SQL will work. This may or may not help
in your case. If not, then you will have to work with Oracle support
because you have a corrupt database.
Best of luck,
-Ari Kaplan
Independent Oracle DBA Consultant
----------------------------------------------------------------------------
(From Allen Moore...)
I wanted to help contribute by providing you with some additional information on
our experiences with Tip #324 regarding the ORA-08102 error "index key not found
"...We've had this error crop up in our database multiple times over the last
six months especially when it came to rebuilding indexes. What we had found
after painless hours of downtime and lots of time with Oracle support was that
this error was caused by a corrupted block on the index. VALIDATE INDEX found
the block for us right away. With that in mind we dropped and recreated the
index and everything returned to normal as far as Oracle was concerned. We also
had Unix admin run a test on that disk to be sure all was well structurally and
it turned out we had some bad sectors on the disk itself. Once replaced we
haven't had the error since.
Again thanks for your site and I hope I in some small way contributed.
Allen Moore, ihov@sleeplessmoon.com
Back to Ari Kaplan's Home Page