June 8, 2010
An interesting thread appeared on the OTN forums today. The original poster indicated that he was running Oracle Database 10.2.0.2 on Windows 2003 R2, and received an unexpected error message:
SQL> analyze table ap2dw.ist_perfaggr_r_7 validate structure cascade; analyze table ap2dw.ist_perfaggr_r_7 validate structure cascade * ERROR at line 1: ORA-01410: invalid ROWID
The OP stated that the table is truncated and reloaded every night and that there are no indexes on the table (the index was dropped and the error remains). The OP bounced the server and the problem disappeared. What was the cause of the problem?
To help you get started, a couple of links:
http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/
http://forums.oracle.com/forums/thread.jspa?threadID=630559
http://forums.oracle.com/forums/thread.jspa?threadID=696740
http://dbaforums.org/oracle/index.php?showtopic=9805
http://www.freelists.org/post/oracle-l/Tracing-invalid-rowid-ora01410,13
http://www.freelists.org/post/oracle-l/Tracing-an-ORA01410-error,6
Server bounced? The problem was windowZ
This might be related, and maybe explains why the database had to be bounced:
In the second comment in this thread I mentioned the following:
http://hoopercharles.wordpress.com/2010/04/29/how-much-space-is-required-to-store-a-whole-lot-of-nothing/
“Dropping and recreating the table put the table into the same set of blocks. Oddly, it seemed that I had to drop/purge the table, bounce the database, and then recreate the table so that the trace file showed the correct information – if I did not bounce the database I saw the block information from the dropped/purged table.)”
In the above article I was attempting to dump the contents of a block when my session was the only user session connected. It almost appeared as though Oracle was reproducing information from a dropped and purged table when asked to perform a database block dump – that phantom information disappeared when the database was bounced. One might wonder if Oracle was still “seeing” the index information in the blocks that had been used by the table’s index, and was throwing the invalid ROWID error due to seeing that information in the blocks.
Just a thought…