sqlldr corrupts my primary key after the first commit

Question

Sqlldr is corrupting my primary key index after the first commit in my ctl file. After the first, no matter what I set the rows value to in my control file, I get:

ORA-39776: fatal Direct Path API error loading table PE_OWNER.CLINICAL_CODE
ORA-01502: index 'PE_OWNER.CODE_PK' or partition of such index is in unusable state
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

I'm using Oracle database and client 11.1.0.6.0.

I know the issue is not due to duplicate rows because if I set the rows directive to a huge value, the index is not corrupt after sqlldr does a single commit for the entire file. This provides me with a workaround, but it's still a little alarming...

Thanks for any guidance anyone can give.

Answer 1

I don't use SQL*Loader much on production tables, but from what I've read, you need to use conventional load.

from the SQL*Loader documentation

When to Use a Conventional Path Load

If load speed is most important to you, you should use direct path load because it is faster than conventional path load. However, certain restrictions on direct path loads may require you to use a conventional path load. You should use a conventional path load in the following situations:

* When accessing an indexed table concurrently with the load, or when
  applying inserts or updates to a nonindexed table concurrently with the
  load

To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes.

Answer 2

I believe the issue was that Oracle did not have time to rebuild the indices on the table in question, so I increased the batch commit size to a number larger than the number of records I was importing.

That fixed the issue.

Answer 3

hi josh , how can i increase the batch commit size