ORA-01502: index or partition of such index is in usable state problem I have a table in my Oracle database, where

select pkcol, count(*) from myTable group by pkcol having count(*) &gt; 1;


yields

  PKCOL   COUNT(*)
------- ----------
      1          2
      2          2


Trying to remove the duplicate rows 

delete myTable where pkcol = 1;


Yields:


  ORA-01502: index 'MYTABLE.PK_MT' or partition of such index is in
  usable state.


I'm using Oracle.DataAccess.Client.OracleBulkCopy to fill the table.

As far as I understand documentation from Oracle PRIMARY KEY constraints had to be checked.

Obviously they are not checked, as I found by doing the same bulkcopy two times in succession which ended in duplicates in all row.

Now I'm only using it after deleting all rows and I'm using a table with a similar primary key as source. As result I expect no problems.

But embedded deep inside my MS Build scripts, I end up with just 2 duplicates out of 2210 rows.

I guess that ignoring the primary key in the first place is a clear bug. No Bulkcopy should be allowed to ignore primary key constraints.

Edit:

Meanwhile I found, that the 2 conflicting rows where normally inserted by some script before bulkcopy was called. The problem reduces to my known problem, that bulkcopy doesn't check primary keys here.
Faced a similar issue.
If you just need to get rid of the error, do:
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

This will output ALTER INDEX ... REBUILD; statements for all &quot;unusable&quot; indexes. Run them, so that the indexes can be &quot;usable&quot; again.