DSXchange

A few of our jobs that runs in production daily, sometimes get aborted with the following error.

"JobNAme..IPInvoiceLkp.IDENT3: ORA-01410: invalid ROWID".

If we reset and rerun, the job will complete successfully. Can anybody help us to sort it out. What I don't understand is, why it runs successfully on the second attempt?

Hi,
It seems that index on the tables have been corrupted. Every index has set of pairs like key id, rowid.
Try droping the index and creating it again.

Thanks
Ketfos

Thanks for the Info. I will have some investigations on this line.

But still, all the time, when this happened, on the second attempt the job went fine. Now I am suspecting whether we miss some data.

I've seen this before and have no idea why it would be happening. Two or three times in the last year one seemingly random job out of the hundreds that run at night would crater with this error. Nothing 'special' about the jobs, just standard insert or update actions on a table via the OCI9 stage.

All we do is 'reset and rerun' and they are fine. Then we forget about it for months until it happens again.

We're not losing any data. Transaction Size is zero in the jobs so all work is rolled back when it aborts. My DBAs tell me nothing is wrong, of course, and it must be a 'DataStage thing'. Ok, right.

I have the same problem as sjacobk mentioned with a simple PX job, also, I get some message as below :

Array Fetch failed Select ......

Index: ODS.Tb_Tran_PK; status: N/A.

It happens once of a few weeks. I can re-run this job without any problem. I wonder if anybody know how to get rid of this issue.


Thanks, xli

At the operating system prompt execute the command This will not only decode the code, but may also recommend remedial action that you can take.

when i do the above i get only

01410, 00000, "invalid ROWID"
// *Cause:
// *Action:

that is it or can i see causes,actions etc

can anyone throw somemore light

Hi Smitha,

Better you try to find out error ORA-01410 on oracle site or take help of your DBA.

Thanks,

--Anupam

You get this error when you perform a SELECT, UPDATE, or DELETE with a clause such as WHERE ROWID = '....', and the ROWID listed not only does not exist in the table, it could NEVER exist in the table.

A ROWID is a combination of the Object ID, File Number, Block Number (within the file), and Row Number (withing the block).

Any given table has a single Object ID, and space allocated in one or more extents - each extent is a contiguous range of blocks in a specified file. If the Object Id embedded in the ROWID does not match that of the table you are SELECTing/UPDATEing/DELETEing, or the file/block does not map to one of that table's extents, then you get an ORA-01410 error.

If you are writing your own SQL that references ROWIDs, then it is probably your own fault - you are trying to use a ROWID pulled from one table on some other table.
Based on the number of people this is happening to, it is more likely either an Oracle error within the RDBMS engine or the OCI toolkit (not actually all that likely), or a DataStage error in the internal OCI code.

If it is possible to obtain this error with any regularity, I would recommend getting your DBA to set Oracle Trace for all jobs on the database. When the error happens again, grab the trace file, find the SQL Statement with the error, establish that it is NOT any of your code, then raise it as a bug with Ascential (attaching the datastage job and the trace file).

I am seeing this same error but I dont have a ROWID selected in my custom SQL. Is it possible for this error to occur when the data volume is huge and the snapshot expires in Oracle DB ?

No, there is a specific ORA error for that - the dreaded 'snapshot too old'.