ORA-1410 Invalid ROWID Mystery

8 06 2010

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?


Actions

Information

3 responses

8 06 2010
8 06 2010
Roberto

Server bounced? The problem was windowZ ;-)

8 06 2010
Charles Hooper

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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: