Re: Ora-1555

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.

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.

Daniel Morgan Received on Wed Mar 12 2003 - 17:47:36 CST