Ora-01410 invalid RowID sporadically in a select statement

corruptionoracle-10gselect

I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this

SELECT distinct t1.crit
  FROM table_name1 t1
 INNER JOIN table_name2 t2
    ON t1.crit = t2.crit
 WHERE NVL (t2.qty, 0) + NVL (t2.adds, 0) - NVL (t2.remove, 0) > 0
   AND t1.process_id = 'some_process_id'
   AND t1.item_no = 'some item NO'
   AND t1.to_gen = 1

where t1 has an index on (item_no, crit), and t2 has an index on (item_no, crit, X), and X is some additional column.

The objective of this statement is to find all candidates for which I need to generate data in additional tables. When the new data has been generated, the column t1.to_gen is set to 0. At the moment it is not clear, if the software which runs this statement, does intermediate commits, e.g. generates some new data for one item_no, sets t1.to_gen = 0 and commits, before using the next item_no from the cursor of the statement in question.

This statement is executed successfully several times a day in my environment, but in rare cases I get an error from oracle, whose source is not clear to me:

ora-01410: invalid RowID

As the select statement does not use the pseudo-column RowID direcly, Oracle will have to use it internally.

What is the source of this problem?

Update: Sad as it sounds, the system does intermediate commits, but that is not the source of the problem.

Best Answer

Are there other processes operating on the same tables? Indexes being rebuilt?

If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/