Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why ORA-1555 snapshot too old.
It didn't make any sense to me either. After he showed me his "current" code
(as opposed to the original code he sent me) we discovered the ORA-1555 was
not coming from the query inside the loop at all. He was trying to query
min and max PK values in a single query at the start of the program. That
caused a full index scan and that's why we were getting ORA-1555. I told him
to get the min and max values in two separate queries and the ORA-1555 went
away. Thanks to all who helped.
-- Chuck "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:apclm7$ghm$2$8300dec7_at_news.demon.co.uk...Received on Mon Oct 28 2002 - 09:39:36 CST
>
> Chuck.
> The developer hasn't done
> 'set transaction read only'
> before the loop has he ?
>
> I can't imagine any way that you can get
> ORA-1555 if you are correct in your statement
> that
>
> > I know the RBS is not wrapping back on itself
> > in that short of a time and it is not shrinking
> > either.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> Chuck wrote in message ...
> >One of my developers is trying to query a 45g table that is
> constantly being
> >insert into. He wants to get a count of rows that meet a certain
> criteria.
> >Of couse he got ORA-1555 snapshot too old. I told him to query
> smaller
> >sections of the table and total up the counts for the individual
> sections so
> >he wrote a PL/SQL block to do that. It loops through table selecting
> 1000
> >rows at a time. Each of these queries finishes in seconds but he is
> still
> >getting ora-1555 though. Why? According to Oracle's documentation
> under the
> >"set transaction" command they use"statement-level read consistency".
> Isn't
> >each separate execution of the select a new statement? I know the RBS
> is not
> >wrapping back on itself in that short of a time and it is not
> shrinking
> >either.
> >
>
>
>