RE: Index in Unusable State: Usual Suspects

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Wed, 10 Jan 2001 08:45:44 -0500
Message-Id: <10737.126258@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_01C07B0B.A649A5B8
Content-Type: text/plain;

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

Hi Stephane,=20

No hint, I saw the code.=20
And the table was not moved, as Tom Terrian had suggested, but that = would
make sense.=20

I will just write it off to another weird occurrence. The developer = knows
what the fix is, should it occur again.

Thanks=20

Lisa

-----Original Message-----
From: paquette stephane [mailto:stephane_paquette_at_yahoo.com] Sent: Wednesday, January 10, 2001 7:25 AM To: Multiple recipients of list ORACLE-L Subject: Re: Index in Unusable State: Usual Suspects

Hi Lisa,

Just guessing.... was there a hint (like append) in the "insert as select" that could have make Oracle use insert with the direct path instead of the conventionnal path ?

"Koivu, Lisa" <lkoivu_at_qode.com> a =E9crit=A0: > Hi all,=20 >=20
> 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. =20
>=20
> The table is not partitioned and this is not a
> bitmap index.
>=20
> 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.=20
>=20
> The client process did not die. =20
> And the database certainly did not experience
> instance failure! I would
> have known about that.
>=20
> 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. =20
>=20
> Thanks in advance for any suggestions.
>=20
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>=20
> V: 954.484.3191, x174
> F: 954.484.2933=20
> C: 954.658.5849
> http://www.qode.com
>=20
> "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."
>=20 >=20

=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com

Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online! http://photos.yahoo.com/
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D INET: stephane_paquette_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C07B0B.A649A5B8
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>RE: Index in Unusable State: Usual Suspects</TITLE>
</HEAD>
<BODY>

Hi Stephane,

No hint, I saw the code. And the table was not moved, as Tom Terrian had = suggested, but that would make sense.

I will just write it off to another weird = occurrence.  The developer knows what the fix is, should it occur = again.

Thanks

<P><FONT SIZE=3D2>Lisa</FONT>
</P>
<BR>

-----Original Message----- From: paquette stephane [<A = HREF=3D"mailto:stephane_paquette_at_yahoo.com">mailto:stephane_paquette_at_yah= oo.com</A>]

<BR><FONT SIZE=3D2>Sent: Wednesday, January 10, 2001 7:25 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: Index in Unusable State: Usual =

Suspects

Hi Lisa,

Just guessing.... was there a hint (like = append)  in
 the "insert as select" that could have = make Oracle use

<BR><FONT SIZE=3D2>insert with the direct path instead of the</FONT>
<BR><FONT SIZE=3D2>conventionnal path ?</FONT>
</P>
<BR>
<BR>

--- "Koivu, Lisa" <lkoivu_at_qode.com> a = =E9crit=A0: > Hi

<BR><FONT SIZE=3D2>all, </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Has anyone ever seen an index rendered unusable =
when</FONT>
<BR><FONT SIZE=3D2>&gt; SQL*Loader is not</FONT>

> involved? > SQL*Loader populates a table that is used = to
 > populate a second table.  One > of the indexes on the second table somehow = became

<BR><FONT SIZE=3D2>&gt; unusable.&nbsp; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; The table is not partitioned and this is not =

a

<BR><FONT SIZE=3D2>&gt; bitmap index.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; There are no savepoints being used, it's just =

an
 > INSERT...SELECT statement > from the first table.  The SQL*Loader = process was

<BR><FONT SIZE=3D2>&gt; indeed complete before</FONT>
<BR><FONT SIZE=3D2>&gt; this statement ran. </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; The client process did not die.&nbsp; </FONT>
<BR><FONT SIZE=3D2>&gt; And the database certainly did not =

experience

<BR><FONT SIZE=3D2>&gt; instance failure!&nbsp; I would</FONT>
<BR><FONT SIZE=3D2>&gt; have known about that.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; 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

<BR><FONT SIZE=3D2>&gt; with no incident.</FONT>
<BR><FONT SIZE=3D2>&gt; However, I need to chase down whatever it was =
that</FONT>
<BR><FONT SIZE=3D2>&gt; caused this.&nbsp; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Thanks in advance for any suggestions.</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>&gt; Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>&gt; Qode.com</FONT>
<BR><FONT SIZE=3D2>&gt; 4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>&gt; Suite G104</FONT>
<BR><FONT SIZE=3D2>&gt; Fort Lauderdale, FL&nbsp; 33319</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>&gt; F: 954.484.2933 </FONT>
<BR><FONT SIZE=3D2>&gt; C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://www.qode.com" =

TARGET=3D"_blank">http://www.qode.com</A> > 
 > "The information contained herein does not = express

<BR><FONT SIZE=3D2>&gt; the opinion or position</FONT>
<BR><FONT SIZE=3D2>&gt; of Qode.com and cannot be attributed to or =
made</FONT>
<BR><FONT SIZE=3D2>&gt; binding upon Qode.com.&quot;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>

<P><FONT SIZE=3D2>=3D=3D=3D=3D=3D</FONT>
<BR><FONT SIZE=3D2>Stephane Paquette</FONT>
<BR><FONT SIZE=3D2>DBA Oracle</FONT>
<BR><FONT SIZE=3D2>stephane_paquette_at_yahoo.com</FONT>
</P>

<FONT =

SIZE=3D2>__________________________________________________</FONT>

Do You Yahoo!? Yahoo! Photos - Share your holiday photos = online!
 <A HREF=3D"http://photos.yahoo.com/" = TARGET=3D"_blank">http://photos.yahoo.com/</A> -- 
 Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A> -- 
 Author: =
=3D?iso-8859-1?q?paquette=3D20stephane?=3D   INET: stephane_paquette_at_yahoo.com

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051 San Diego, =
California        -- Public Internet = access / Mailing Lists
 <FONT =

SIZE=3D2>---------------------------------------------------------------=

-----
 To REMOVE yourself from this mailing list, send an = E-Mail message
 to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in
 the message BODY, include a line containing: UNSUB = Received on Wed Jan 10 2001 - 07:45:44 CST