Oracle Load Error

[thebird]

Hi,

I am trying to load an Oracle table. The job is a simple load job, with the Oracle enterprise stage write method set to Load and write mode set to Append.

I have set the index mode option to Rebuild, Add NoLogging clause to True and Add ComputeStatistics to True.

The job throws out a few fatal errors, but the job status reports Finished.

The Director gives me the following information -

LoadTransSurrKeyLkp,1: Load completed - logical record count 81355.
LoadTransSurrKeyLkp,1: Export complete; 81355 records exported successfully, 0 rejected.

The fatal errors follow the above info in the director -

LoadTransSurrKeyLkp: Oracle call failed; sqlcode = -1418; message: ORA-01418: specified index does not exist

LoadTransSurrKeyLkp: ExecuteImmediate failed for:
alter index SYSTEM.XPKTRANS_SURRKEY_LOOKUP rebuild parallel nologging compute statistics.

LoadTransSurrKeyLkp: Index `SYSTEM.XPKTRANS_SURRKEY_LOOKUP' on table `ESAIMSTR.TRANS_SURRKEY_LOOKUP' has NOT been rebuilt.

After these fatal errors, there is an info which says- LoadTransSurrKeyLkp: Index: SYSTEM.XPKTRANS_SURRKEY_LOOKUP; status: UNUSABLE.

When I check the Oracle Table through TOAD, and do a select all from table it throws the following error -
ORA-01502: index 'SYSTEM.XPKTRANS_SURRKEY_LOOKUP' or partition of such index is in unusable state

I think the issue is related to the indexing or partitioning of the table and so when I checked the indexing of the Table through TOAD, it showed the status as "Unusable".

Can anyone please help me out with this issue. Is there something that can be done through DataStage or does this issue have to be resolved by a DBA.

If it can be done through DS, please tell me what to do?

regards,

The Bird.

[ray.wurlod]

Initially, at least, this has to be investigated by the Oracle DBA, who may need to drop and re-create the index. A search on this site for "unusable" or for -1418 may throw more light on what's needed for your information.

[chulett]

Failures during a direct path load will leave indexes in an UNUSABLE state, which a rebuild will be needed to fix.

As to why there were failures... definitely get your DBA involved. Show them what SQL is being generated by the stage for each of the steps involved. I suspect it may have something to do with the index being owned by SYSTEM rather than ESAIMSTR, but that's just a guess at this point.