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.
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.
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.
