Oracle Load Problem

[kashif007]

I am trying to load into Oracle using Oracle Enterprise stage. The write method is load. The target table is indexed on three fields plus it is partitioned by 12 months. When I try to load/append the data (sequentially) to this table the job aborts complaining about the index partition that are not getting created on the table during or after the load. Following is the warning and load error that I get.

Oracle_Enterprise_31,0: ORA-26027: unique index IBDD_CASPR.ACCT_X_OFFR_INX01 partition NOV08 initially in unusable state

ORA-26027: unique index IBDD_CASPR.ACCT_X_OFFR_INX01 partition NOV08 initially in unusable state

What should be the best way to load an oracle table when the table is indexed as well as partitioned ?

Please advice.

[chulett]

It won't even start because starting off you have an index in an unusable state. Have your DBA fix that and then see what happens.

[kashif007]

When I truncate the table and run fresh data, the job runs fine and records get uploaded into the table, but when I rerun the job to process another months data it loads some data and aborts complaining about the Index parition error mentioned earlier. We cannot recreate the index partition after the each load in PROD.

I tried using Rebuilt/maintainence option in the oracle Enterprise stage in parallel mode but the job aborts. Will their be any other workaround.

Regards

[chulett]

Unique indexes get left in an UNUSABLE state after a load because they no longer contain unique values. That's the first thing I would look into, your initial truncated load is not working as well as you think.

[theone]

Parallel loading into oracle table with indexs will make break indexs, you have to use conventiaonal way to load into that, you can use environmental variable APT_ORACLE_LOAD_OPTIONS = OPTIONS(DIRECT=FALSE,PARALLEL=FALSE)

[chulett]

Not really, you need to make sure index maintenance is enabled and that you don't have duplicate values in unique indexes.