In data warehouses it is not unusual to set indexes unusable before a load operation and rebuild them after the load - this approach may reduce the maintenance overhead for the indexes significantly. But for a unique b*tree index this is not an option:
drop table t;
create table t( id number);
create unique index t_idx on t(id);
alter index t_idx unusable;
insert into t
select rownum id
from dual
connect by level < 10;
*
FEHLER in Zeile 1:
ORA-01502: Index 'C##TEST.T_IDX' oder Partition dieses Index in nicht brauchbarem Zustand
--> index ... or partition of such index is in usable state.
With a nonunique index the script works without a problem. The result of this behaviour is frequently that such indexes are dropped before and recreated after the data integration - and that's not a satisfying workaround.