UNUSABLE index after SQLLDR Direct Path Load

Indexes Left in Index Unusable State

SQL*Loader will leave indexes in Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.

Any SQL statement that tries to use an index that is in Index Unusable state returns an error. The following conditions cause the direct path option to leave an index or a partition of a partitioned index in Index Unusable state:

  • SQL*Loader runs out of space for the index, and cannot update the index.

  • The data is not in the order specified by the SORTED INDEXES clause.

  • There is an instance failure, or the Oracle shadow process fails while building the index.

  • There are duplicate keys in a unique index.

  • Data save points are being used, and the load fails or is terminated by a keyboard interrupt after a data save point occurred.

To determine if an index is in Index Unusable state, you can execute a simple query:

<a name="1121"></a>SELECT INDEX_NAME, STATUS
<a name="5670"></a>   FROM USER_INDEXES 
<a name="5671"></a>   WHERE TABLE_NAME = '<em class="Italic">tablename</em>';
<a name="5674"></a>

To determine if an index partition is in unusable state,

<a name="2811"></a>SELECT INDEX_NAME, <br /><a name="5675"></a>       PARTITION_NAME,<br /><a name="5676"></a>       STATUS FROM USER_IND_PARTITIONS<br /><a name="5677"></a>       WHERE STATUS != '<em class="Italic">VALID</em>';<br /><a name="5680"></a><br /><br />

select distinct(status) from user_indexes

alter session set skip_unusable_indexes = true; –OR SQLLDR option SKIP_INDEX_MAINTENANCE = TRUE
select ‘alter index ‘ || u.index_name || ‘ unusable;’ from user_indexes u;

FROM user_indexes u WHERE status = ‘UNUSABLE’ AND  UPPER(table_name) = ’employees’;

set pagesize 0

alter session set skip_unusable_indexes = true;
spool c:\temp\disable_indexes.sql
select ‘alter index ‘ || u.index_name || ‘ unusable;’ from user_indexes u;
spool off

–do import, sqlldr

select ‘alter index ‘ || u.index_name ||
‘ rebuild online;’ from user_indexes u;


About this entry