Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  bernd_k Jul 11 '11 at 10:15
    
Yes it is a slightly odd definition of "enabled" isn't it :) –  Jack Douglas Jul 11 '11 at 10:20
add comment

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.

(Shamelessly copied from: http://www.squaredba.com/ora-01502-index-or-partition-of-such-index-is-in-unusable-state-145.html :-))

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.