OCI Stage: Indexes in Unusable State

[Munish]

Hi Champs,
I am using Oracle Load to load my fact table.
Volume: 280 mills per load.

The DB is partitioned by date.
We have 7 bitmap indexes on this table.

The problem starts after the data is loaded and when OCI stage tries to rebuild the indexes and it is failing there.

The error:
ORA-01502: index 'CBA_DBA.XIF1EDX_RPT_SDF_BMAP' or partition of such index is in unusable state

Any Guesses........

[ray.wurlod]

Don't need to guess. Need to Search.

This is a known issue, and has been discussed here previously.

[I_Server_Whale]

It can't get any better than Craig's answer.

Bitmap indexes are notoriously picky and prone to going bad during a load. As people have said, have a chat with your DBAs but I'd bet you'll need to drop them before the load and then rebuild them post load.

Also, check this POST.

[Munish]

I went through the search and found it very informative.
However,
I am just wondering if other than duplicate primary keys, break in referential integrity can cause this error.

Thanks,
Munish

[narasimha]

Are you doing a bulk load with direct/parallel mode?

[Munish]

Yes

[crouse]

We get this problem when Oracle runs out of temp space. The temp space is used to store/sort/create your indexes as the data is being loaded. Google for this error and you'll find ways to calculate how much temp space is needed. And other tricks like sorting the input data in the order of (one of) you indexes.

Are DataStage and Oracle on the say server, or at least on the same version of operating system.

-Craig

[chulett]

You can get this problem when pretty much any error happens during a direct path bulk load. That's the price you pay for the speed of a direct path load - any problem will leave indexes in an UNUSABLE state.