If you want to load data in direct mode into a table you should set all dependent indexes in UNUSABLE state. Session parameter SKIP_UNUSABLE_INDEXES set to TRUE enables to ignore indexes with status UNUSABLE during direct load.

However If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

Test data, check parameter

select name, value from v$parameter
where name='skip_unusable_indexes';

NAME                   VALUE
---------------------- ------
skip_unusable_indexes  TRUE

create table test_tbl
(
  id1 number,
  id2 number
);

create unique index test_tbl_idx1 on test_tbl(id1);

Try to set index to UNUSABLE state

alter index test_tbl_idx1 unusable;

select index_name, uniqueness, status from user_indexes 
where table_name='TEST_TBL';

INDEX_NAME                     UNIQUENESS STATUS 
------------------------------ ---------- --------
TEST_TBL_IDX1                  UNIQUE     UNUSABLE

Loading data in direct mode

insert /*+ APPEND */ into test_tbl
select rownum, rownum 
from all_users;

SQL Error: ORA-26026: unique index TEST_TBL_IDX1 
initially in unusable state

In this case to avoid this error UNIQUE index should be dropped just before loading data and recreated after load.

drop index test_tbl_idx1;

insert /*+ APPEND */ into test_tbl
select rownum, rownum 
from all_users;

create unique index test_tbl_idx1 on test_tbl(id1);

You can often encounter this problem for primary key constraint , if the constraint relay on manually created unique index. When primary key constraint is disabled then manually created index is preserved (not dropped) and even set to UNUSABLE state can cause ORA-26026 for direct loads.

Have a fun 🙂

Tomasz