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_01C07B02.4D2252E0
Content-Type: text/plain;
charset="iso-8859-1"
Yes, If you issue an "ALTER TABLE MOVE" command, the indexes will be marked
unusable.
Tom Terrian
-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_qode.com]
Sent: Tuesday, January 09, 2001 2:42 PM
To: Multiple recipients of list ORACLE-L
Subject: Index in Unusable State: Usual Suspects
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 <
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_01C07B02.4D2252E0
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Index in Unusable State: Usual Suspects</TITLE>
<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=178084112-10012001>Yes, If you issue an "ALTER TABLE MOVE" command,
the indexes will be marked unusable.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=178084112-10012001></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=178084112-10012001>Tom
Terrian</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Koivu, Lisa
[mailto:lkoivu_at_qode.com]<BR><B>Sent:</B> Tuesday, January 09, 2001 2:42
PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Index
in Unusable State: Usual Suspects<BR><BR></DIV></FONT>
<P><FONT face=Arial size=2>Hi all, </FONT></P>
<P><FONT face=Arial size=2>Has anyone ever seen an index rendered unusable
when SQL*Loader is not involved?</FONT> <BR><FONT face=Arial size=2>SQL*Loader
populates a table that is used to populate a second table. One of the
indexes on the second table somehow became unusable. </FONT></P>
<P><FONT face=Arial size=2>The table is not partitioned and this is not a
bitmap index.</FONT> </P>
<P><FONT face=Arial size=2>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. </FONT></P>
<P><FONT face=Arial size=2>The client process did not die.
</FONT><BR><FONT face=Arial size=2>And the database certainly did not
experience instance failure! I would have known about that.</FONT> </P>
<P><FONT face=Arial size=2>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. </FONT></P>
<P><FONT face=Arial size=2>Thanks in advance for any suggestions.</FONT> </P>
<P><B><FONT face=Arial size=1>Lisa Rutland Koivu</FONT></B> <BR><FONT
face=Arial size=1>Oracle Database Administrator</FONT> <BR><FONT face=Arial
size=1>Qode.com</FONT> <BR><FONT face=Arial size=1>4850 North State Road
7</FONT> <BR><FONT face=Arial size=1>Suite G104</FONT> <BR><FONT face=Arial
size=1>Fort Lauderdale, FL 33319</FONT> </P>
<P><FONT face=Arial size=1>V: 954.484.3191, x174</FONT> <BR><FONT face=Arial
size=1>F: 954.484.2933 </FONT><BR><FONT face=Arial size=1>C:
954.658.5849</FONT> <BR><FONT face=Arial size=1><A href="
http://www.qode.com"
target=_blank>
http://www.qode.com</A></FONT> </P>
<P><I><FONT color=#000000 face=Arial size=1>"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."</FONT></I></P></BLOCKQUOTE></BODY></HTML>
Received on Wed Jan 10 2001 - 06:38:58 CST