Direct Load Problems in Oracle Enterprise stage

[bmsq]

Hi all,

We have been trying to solve a problem with using the direct loader in the oracle enterprise stage for awhile now. I've already done alot of searching on the forum to try and solve this myself but with no luck.

The table we are trying to load is partitioned and has a number of bitmap indexes applied. All of the indexes are local range partitioned except the primary key which defaults to a global index.

So far, I've managed to get data to be loaded into the Database using "Disable Constraints = true" and "Index mode = Maintenance" but this has caused errors when trying to re-enable the constraints:

Code: Select all

orclDetailFact: Oracle call failed; sqlcode = -604; message: ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'CBA_DBA.I1_DETAIL_FACT' or partition of such index is in unusable state

orclDetailFact: ExecuteImmediate failed for:
alter table DBA.RPT_DETAIL_FACT enable constraint FK_DETAIL_FACT_1.

If I use "Index mode = Maintenance" with "Disable Constraints = false" then I get this error:

Code: Select all

orclDetailFact: All indexes on table `RPT_DETAIL_FACT' are not local range partitioned indexes;
therefore, the -index maintenance option cannot be used.

I know what this is saying, but I don't know how to make the PK use local indexes rather than global. I can't drop the index without dropping the PK. And when I create the PK, Oracle 9i creates a global index by default.

Can I also assume that I'm getting the "unusable state" error with "Disable Constraints = true" because DS is assumeing that we are using local indexes? I've also used upsert instead of load to confirm that our data does not contain any errors which break constraints.

Please help! I'm out of ideas of how to fix this.

[ray.wurlod]

Were there any failures (aborted jobs) that may have left the bitmap indexes in an unusuable state? Get your Oracle DBA to check and, perhaps, to repair them, then try again. Have the DBA trace what is happening, to try to capture what it is that is damaging the bitmap indexes.

[bmsq]

No other jobs fail prior to running this job. This job uses a dataset which is created by an earlier job and just loads it into the database. We can rebuild the indexes and re-run just this job and the db will be in an unusable state again.

All bitmap indexes become unusable. There are a total of 15 indexes. We have also managed to make the primary key use local indexing. Oracle wouldn't allow us to do it initially because we partition based on the field DATE_KEY while the primary key is SERVICE_CDR_KEY. To get around this, we made SERVICE_CDR_KEY & DATE_KEY a primary key combination which allowed us to make it local.

This has allowed us to use DataStage with the "Disable Constraints = false" property and DataStage completes with no error. Unfortunately, the all bitmap indexes are still left in an unstable state.

Any thoughts? I'm completely baffled.

[ray.wurlod]

Search here and at IBM DeveloperWorks - I recall seeing something about bitmaps being left in unusable state but don't have the time to pursue it.