FreeLists / oracle-l / Re: Index unusable

["Radoulov, Dimitre" <cichomitiko@xxxxxxxxx>]

> 2x this week a non-partitioned pk index has become unusable preventing
> inserts into the table.
> 
> there has been no ddl on the table.


Given the question, I suppose that the table is not partitioned too, so:


SQL> create table t(a number);

Table created.

SQL> alter table t add constraint t_pk primary key (a);

Table altered.

[...]

$ cat test.ctl 
LOAD DATA
INFILE *
INTO TABLE T
(a)
BEGINDATA
1
1
$ sqlldr test/test control=test.ctl direct=y

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Dec 1 16:38:30 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Load completed - logical record count 2.

SQL> select * from t;

         A
----------
         1
         1

SQL> select status from user_indexes where table_name='T';

STATUS
--------
UNUSABLE



Regards
Dimitre