Ora-01410 - the answer

A few weeks ago I posted a question about a query which was intermittantly returning an ORA-01410 (invalid rowid) error. What mystified me about this was that the query was not explicitely selecting by rowid. I used analyze to check all indexes on the table referenced in the error message and found no corrupted indexes.

My only clue was a google search on "ORA-01410" which yielded a response from Jonathan Lewis to someone who was having a similar problem ( http://www.freelists.org/archives/oracle-l/08-2006/msg00075.html ). He seemed to be saying that you could get this error if an index was being rebuilt while the query was running. I had some discussions with our application people and eventually learned that the software which runs every hour to update this database, a data warehouse, from the production database was dropping indexes before the update and recreating them after the update. I just finished testing this on our QA database ( start the query, drop and recreate the index) and got the same error which the user had reported.

This is Oracle 8.1.7.0 on SunOS 5.9.

Thanks Jonathan.

Hope this will help someone else.

Peter Schauss

--
http://www.freelists.org/webpage/oracle-l