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.

  • Are you sure the error is produced by the SELECT and not some other statement in your code? Sep 17, 2012 at 10:12
  • Are you issuing commits and continuing to use your cursor after doing so? That's a very bad habit. Sep 17, 2012 at 14:10
  • @ypercube: yes I am sure, our exception handling writes the error code and the causing statement into a log-file.
    – mruether
    Sep 17, 2012 at 15:21

1 Answer 1


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: https://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/

Consider this: if you rebuild an index (whether or not you use the online option) Oracle will copy the index contents from one location to another and, when the copy is complete, free up the space that held the original index contents.

What’s going to happen to a long running query that started running before you did your rebuild? Surprisingly the query will carry on running using the “old” index. Oracle introduced a form of ‘cross-DDL read consistency’ many years ago to make partition maintenance operations possible, and that’s the feature that allows your query to carry on running.

But what’s going to happen if some other process now demands the space and puts something into it – for example by creating or extending a table or index. The next time your long-running query hits the “old” index it may find itself looking at a block that contains the wrong type of data for the wrong object id. And so you get Oracle error ORA-01410: invalid rowid.

  • 1
    That's it! The system started an index rebuild while the select statement ran.
    – mruether
    Sep 20, 2012 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.