Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

ODI Error: Missing Index Keyword

Received Response
32
Views
5
Comments
3140278
3140278 Rank 1 - Community Starter
in Oracle Business Intelligence Applications

Hi Gurus,

ODI fails due to

ORA-00968: missing INDEX keyword (from the W_ETL_EVENT_LOG), table, when I debug it, it's trying to execute the below command:

CREATE BITMAP UNIQUE INDEX DEV1_DW.WC_RECEIPT_F_U1 ON DEV1_DW.WC_RECEIPT_F (DATASOURCE_NUM_ID, INTEGRATION_ID) PARALLEL NOLOGGING;

SQL Error: ORA-00968: missing INDEX keyword

00968. 00000 -  "missing INDEX keyword"

as we can see the command is not correct as BITMAP and UNIQUE cannot work together.

I did create the Index manually on the table removing the BITMAP command, but ODI generating the above command. Do we have any way where ODI while running the interface will not use the BITMAP command and use

CREATE UNIQUE INDEX DEV1_DW.WC_RECEIPT_F_U1 ON DEV1_DW.WC_RECEIPT_F (DATASOURCE_NUM_ID, INTEGRATION_ID) PARALLEL NOLOGGING;


Note: The same interface ran successfully in the other Development Environment from where I did a SMART Export Import.


Quick help will be much appreciated.

Thanks

Amit

Answers

  • 841717
    841717 Rank 2 - Community Beginner

    Hi Amit, Navigate to the datastore in ODI Designer -> Models accordion. Locate the target table, expand constraints -> find WC_RECEIPT_F_U1. Open it and go to flexfields tab. Flexfield named OBI Bitmap Index will probably be Y.

    If you change this to N, it will not use BITMAP clause when creating index.

  • 3140278
    3140278 Rank 1 - Community Starter

    Hi,

    I have already tried that unchecking the OBI BITMAP INDEX, but the code generated the BITMAP keyword and fails while creating Indexes

    I also tried changing the Primary Key to Alternate Key or a Non-Unique Index. It gives me a same error.

    I was trying to explore if there was anything else i need to do or check.

    Thanks

    Amit

  • 841717
    841717 Rank 2 - Community Beginner

    Hi, How are you running this after making the change in model. Are you running the task through LP or standalone scenario. I ask this because if run through LP, there is a step in Initialize Batch Load phase which refreshes a metadata table that has all index definitions. It is from there that I believe it picks up these index definitions. If run standalone, it would still pick up the old definition.

  • 3140278
    3140278 Rank 1 - Community Starter

    Hi,

    I am running the standalone scenario, and i think you are right its picking up the old definition. How to refresh so that i picks up the new definition. I need to first successfully run it in the standalone mode and then will need to create a Custom Fact Group and then create the Custom Load plan, as we created new custom table for this.

    Question : How do i refresh the metadata in the standalone run.

    Regards,

    Amit

  • 3058289
    3058289 Rank 1 - Community Starter

    Thank you to 841717. We can refresh W_ETL_ALL_INDEXES by running the scenario: EXEC_LOAD_INDEX_METADATA. This is found in BI Apps Project > Components > DW > Oracle > Load Index Metadata