ORA-20000: Error creating indexes in parallel, see W_ETL_EVENT_LOG for details

I see an error ODI when running OBIA etl loads in ODI.

ODI-1228: Task Custom_SIL_TestFact.WC_TEST_F (Integration) fails on the target ORACLE connection BIAPPS_DW.

Caused By: java.sql.SQLException: ORA-20000: Error creating indexes in parallel, see W_ETL_EVENT_LOG for details

ORA-06512: at line 119

Looks like duplicates in WC_TEST_F table. But real problem is incorrect flex-fields setting in the WC_TEST_F data store.

Issue:

There is an index created WC_TEST_F_U1 as alternate key and "OBI Bitmap Index" value set as Y.

Hence it is issuing below query for creating index during load.

CREATE UNIQUE BITMAP INDEX DEV_DW.WC_TEST_F_U1 ON DEV_DW.WC_TEST_F (DATASOURCE_NUM_ID, INTEGRATION_ID) PARALLEL NOLOGGING

The syntax is incorrect because a BITMAP index cannot be unique

Resolution:

1.Update W_ETL_ALL_INDEXES table  and set the PRE_PARAMETERS column from ‘UNIQUE BITMAP ‘ to ‘UNIQUE ‘ for this index WC_TEST_F_U1.

update W_ETL_ALL_INDEXES set pre_parameters='UNIQUE ' where index_name='WC_TEST_F_U1';

2.Modify WC_TEST_F data store flex-fileds to mark the ‘ODI Bitmap Index’ Parameter as ‘N’.

Restart load.