Index in Unusable State: Usual Suspects

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Tue, 9 Jan 2001 14:42:39 -0500
Message-Id: <10736.126190@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C07A74.57400102
Content-Type: text/plain;

charset="iso-8859-1"

Hi all,

Has anyone ever seen an index rendered unusable when SQL*Loader is not involved?
SQL*Loader populates a table that is used to populate a second table. One of the indexes on the second table somehow became unusable.

The table is not partitioned and this is not a bitmap index.

There are no savepoints being used, it's just an INSERT...SELECT statement from the first table. The SQL*Loader process was indeed complete before this statement ran.

The client process did not die.
And the database certainly did not experience instance failure! I would have known about that.

I see that a unique index can become unusable if there are duplicate values, but that isn't the case here. The index was rebuilt with no incident. However, I need to chase down whatever it was that caused this.

Thanks in advance for any suggestions.

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

------_=_NextPart_001_01C07A74.57400102
Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>Index in Unusable State: Usual Suspects</TITLE>
</HEAD>
<BODY>

Hi all, 


Has anyone ever seen an index rendered =
unusable when SQL*Loader is not involved?
 SQL*Loader populates a table that is =
used to populate a second table.  One of the indexes on the second = table somehow became unusable.

The table is not partitioned and this =
is not a bitmap index.


There are no savepoints being used, =
it's just an INSERT...SELECT statement from the first table.  The = SQL*Loader process was indeed complete before this statement ran. =


The client process did not die.  =

 And the database certainly did not =
experience instance failure!  I would have known about = that.


I see that a unique index can become =
unusable if there are duplicate values, but that isn't the case = here.  The index was rebuilt with no incident. However, I need to = chase down whatever it was that caused this.

Thanks in advance for any =
suggestions.


Lisa Rutland Koivu
 Oracle Database Administrator
 Qode.com
 4850 North State Road 7
 Suite G104
 Fort Lauderdale, FL  =
33319


V: 954.484.3191, x174
 F: 954.484.2933 
 C: 954.658.5849
 <A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A>


"The =
information contained herein does not express the opinion or position = of Qode.com and cannot be attributed to or made binding upon = Received on Tue Jan 09 2001 - 13:42:39 CST