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(*) > 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 "unusable" indexes. Run them, so that the indexes can be "usable" again.

StackExchange Database Administrators Q#3754, answer score: 13