ORA-01502: index or partition of such index is in usable state problem

Question

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.

Answer 1

From the documentation you link to:

UNIQUE constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE constraint.

They way I read it, the same applies to PRIMARY KEY constraints though the wording is a little ambiguous. You might not like this behaviour, but it is not "a bug" as it is behaving as designed - and there are other ways of ending up with this sort of 'broken' constraint.

See this OTN post for more information and an approach that might work better for you using pl/sql and forall ... save exceptions.

• bernd_k: Perhaps I misinterpreted the preceding sentence: During an Oracle bulk copy, the following constraints are automatically enabled by default:NOT NULL, UNIQUE, PRIMARY KEY (unique-constraints on not-null columns). The problem is that Oracle.DataAccess.Client.OracleBulkCopy contrary to Data.SqlClient.SqlBulkCopy has no BulkCopyOption CheckConstraints, which makes it difficult to use.
• Jack Douglas: Yes it is a slightly odd definition of "enabled" isn't it :)

Answer 2

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.

Answer 3

A short extension to Frosty Z's answer above. If you are not SYSDBA but you are experiencing the same problem within your own schema, you can use the following code:

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;'
 FROM USER_INDEXES
 WHERE STATUS = 'UNUSABLE';

Tested with Oracle XE 18c and SQL*Plus 18.4.

Answer 4

Go to the indexes folder and find the index file that gives you the error in your case MYTABLE.PK_MT, then click on Actions -> Rebuild.
That worked for me.