Re: What would cause ORA-1410 (invalid rowid) on a simple select?

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Fri, 31 Aug 2007 17:06:12 GMT
Message-ID: <8UXBi.104$3R5.35@trnddc05>

Anurag Varma wrote:
> On Aug 30, 4:56 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:

>> Anurag Varma wrote:
>>> On Aug 30, 4:02 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:
>>>> Oracle 10.2.0.2 64 bit, Solaris 10.
>>>> Here's the select? Occasionally we get an ORA-1410 on it but 99.9% of
>>>> the time it works fine. It's coming from a web based application that
>>>> uses a product called hibernate to access to the database.
>>>> SELECT DISTINCT flipbook2_.name AS col_0_0_
>>>> FROM flip_page_media flippageme0_,
>>>>     flip_page flippage1_,
>>>>     flipbook flipbook2_
>>>>     INNER JOIN user_content flipbook2_1_
>>>>        ON flipbook2_.content_id = flipbook2_1_.content_id
>>>>     INNER JOIN content flipbook2_2_
>>>>        ON flipbook2_.content_id = flipbook2_2_.content_id
>>>> WHERE flippage1_.content_id = flipbook2_.content_id
>>>>   AND flippageme0_.flip_page_id = flippage1_.flip_page_id
>>>>   AND flippageme0_.media_id = :1
>>>> I have checked the indexes used for corruption and found none.
>>> Simple selects should not give a ORA-1410.
>> That's what I thought too. The only time I've even seen this before was
>> with incorrect use of a "where current of" cursor.
>>
>>> How did you check the indexes for corruption?
>>> Did you issue an alter table <table> validate structure cascade; ? to
>>> see
>>> if there is a rowid mismatch between the table and index.
>> Yes. I also ran dbv on the datafiles in the index tablespaces.
>>
>> I am wondering if there is a with the INNER JOIN syntax. That same
>> syntax has caused problems in defining materialized views before and I
>> had to convert it to the older style join syntax before it would work.
>> Unfortunately in this case it's being generated by a 3rd party product
>> from behind the scenes.

>
>
> Are these regular tables being referred to in the query
> .. or are they views?
> If views .. do they point to a remote database?
>
> I don't see why INNER JOIN syntax should be suspected as
> a cause of ORA-1410. We too have one application using
> hibernate without any ORA-01410 issues...
>
> If none of the above applies in your situation .. you
> might be better off opening a SR...

All tables are normal tables. I opened an SR.

The only reason I mentioned the inner join syntax is because there is a known issue when using that to define a materialized view. I think it was that you couldn't use a synonym name with that syntax when creating an mview or it would raise an error.

I thought maybe hibernate was trying to do some rowid caching and later trying to access those rows after some other app had deleted them. Some times these tools get to fancy for the own good. I really don't know much about hibernate though, not being a developer, and just getting pulled cold into this issue. Received on Fri Aug 31 2007 - 12:06:12 CDT