Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index in Unusable State: Usual Suspects

RE: Index in Unusable State: Usual Suspects

From: Terrian, Thomas <Thomas.Terrian_at_standardregister.com>
Date: Wed, 10 Jan 2001 07:38:58 -0500
Message-Id: <10737.126253@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_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,&nbsp; 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>&nbsp;</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.&nbsp; One of the   indexes on the second table somehow became unusable.&nbsp; </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.&nbsp; 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.&nbsp;
</FONT><BR><FONT face=Arial size=2>And the database certainly did not
  experience instance failure!&nbsp; 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.&nbsp; The index was   rebuilt with no incident. However, I need to chase down whatever it was that   caused this.&nbsp; </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&nbsp; 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US