Re: Ora-1555

On Wed, 12 Mar 2003 15:47:36 -0800, DA Morgan <damorgan_at_exesolutions.com> wrote:

>GC wrote:
>
>> Have a very strange ora-1555 error.
>>
>> In the simplest terms, here is what is happening:
>> 1. Long-running (~3 hours) query is run against table A, B, and C.
>>
>> 2. No data is updated, inserted, or deleted in tables A, B, or C, thus
>> no blocks are placed into rollback.
>>
>> 3. Since there is no data in rollback, Oracle does not need to
>> reference blocks in the rollback segments.
>>
>> 4. Table Z, completely unrelated to tables A, B, or C, is loaded from
>> table Z_tmp via the merge function in sql.
>>
>> 5. The query merrily runs against tables A, B, and C until this merge
>> job (on table Z) runs.
>>
>> 6. An ora 1555 is thrown and the query fails.
>>
>> Why would an ora 1555 be thrown on a query that is not referencing any
>> blocks in any rollback segments? I have a tar open and am running
>> systemstate and errorstack traces when this issue replicates itself,
>> but has anyone here had a similiar problem or can fill what I may be
>> missing?
>>
>> Database is a 9.0.1.4 64bit system running on hp-ux 11. Somewhat large
>> batch system with ample rollback space (20 rollback segs, 13 GB usable
>> rollback space - perhaps overkill, but it was like this when I got
>> here and I don't want to give up disk space if I don't have to :)
>>
>> Thanks,
>> Greg
>
>Almost undoubtedly nothing is running for 3+ hours and not using rollback.
>Why don't you post the SQL and let us take a look.

Curious statement - why would a simple extract query (I'll have to wait until morning to post it, if it comes to that) generate rollback? Temp thrashing perhaps, memory sorts definitely, but rollback? It's not even updating background data dictionary tables. I'm not arguing per se, I'm just curious as to why rollback would be generated in this situation.

>Oh and get yourself out of 9.0.1. It was never intended for production and
>what you are hitting might be an early bug.

9.0.1 was bad, but the latest 9.0.1.4 patchset seems to have taken care of many of the nasty bugs. Coworkers using 9.2.0.2 are having a worse time than I :)

Cheers,
Greg Received on Wed Mar 12 2003 - 18:00:20 CST