Load failed due to an index partition in unusable state

In Feb, I helped the on-call DBA resolve an issue about index in unusable state. DBA got called by app team:

" One of our load got failed with the below error please look into this and let us know.
database [], user []

WRITER_1_*_1> WRT_8229 Database errors occurred:
ORA-01502: index 'DW_FIOS.IX01_FTV1_FACT_BL_DTL_PRD' or partition of such index is in unusable state
"
We checked the index/partions status by issue the following sql:

select index_name, partition_name, status
from dba_ind_partitions
where index_name='IX01_FTV1_FACT_BL_DTL_PRD';

We found that there was an index partition in unsuable state:

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IX01_FTV1_FACT_BL_DTL_PRD      P20080131                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080219                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080220                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080221                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080222                      UNUSABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080223                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080224                      USABLE
IX01_FTV1_FACT_BL_DTL_PRD      P20080225                      USABLE

We rebuild the index partition by issue:

alter index DW_FIOS.IX01_FTV1_FACT_BL_DTL_PRD rebuild partition P20080131 parallel 4 nologging;

After rebuild, we asked the app team re-load data and the job has completed without any problem.


I am not sure how this load data process was designed at the first place. In some case, we may want to rebuild the indexes after the load in order to speed up the load. This may be achived by:
1. Setting the index to unusable
2. At the session level: alter the session set skip_unusable_indexes = true;
3. Load the data
4. Rebuild the index



===
The following test is done in 11g, it seems we can insert into a table that has an index in ususable state:


dennis@TESTDB11> drop table t;

Table dropped.

dennis@TESTDB11> create table t(col1 number, col2 number);

Table created.

dennis@TESTDB11> insert into t values(1,2);

1 row created.

dennis@TESTDB11> insert into t values(2,3);

1 row created.

dennis@TESTDB11> insert into t values(4,5);

1 row created.

dennis@TESTDB11> create index t_idx1 on t(col1);

Index created.

dennis@TESTDB11> select index_name, status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T_IDX1 VALID
...

dennis@TESTDB11> alter index t_idx1 unusable;

Index altered.

dennis@TESTDB11> select index_name, status from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
T_IDX1 UNUSABLE

dennis@TESTDB11> insert into t values(4,5);

1 row created.

dennis@TESTDB11> commit;

Commit complete.

dennis@TESTDB11> insert into t values(6,5);

1 row created.

dennis@TESTDB11> select * from t;

COL1 COL2
---------- ----------
1 2
2 3
4 5
4 5
6 5