SQLLDR and skip_unusable_indexes

Question

I have a table with a Unique Index that I need to load 800,000 rows into.

I altered the index and made it unuseable. I added 'options( rows=4000, bindsize=512000, skip_unusable_indexes=TRUE )' to my control file.

I'm still getting errors of the form:

ORA-01502: index 'x_Unique' or partition of such index is in unusable state

Suggestions welcome

Answer 1

You cannot do it with unique indexes. See SKIP_UNUSABLE_INDEXES:

However, indexes that are unique and marked IU are not allowed to skip index maintenance. This rule is enforced by DML operations, and enforced by the direct path load to be consistent with DML.

You probably should drop your index and recreate it after.

Answer 2

I think that you could replace the unique index with a non-unique index and then place a unique constraint on the same column(s). Then the constraint can be disabled and the index made unusable before the load, then the index rebuilt and the constraint re-enabled afterwards.