Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-1555 snapshot too old

Re: ORA-1555 snapshot too old

From: <sybrandb_at_my-deja.com>
Date: Fri, 06 Oct 2000 06:34:11 GMT
Message-ID: <8rjrp3$js6$1@nnrp1.deja.com>

In article <3J7D5.19638$NY2.548166_at_news.infostrada.it>,   "Antonio Sant" <asant_at_iol.it> wrote:
> Hi there!
>
> How can I resolve this problem?
>
> After a talk with our DBA the suggestion was to translate this:
>
> For tmp in (select ... ) Loop
> ....
> commit;
> end loop;
>
> in this
>
> For tmp in (select ... ) loop
> temp_table(i):=tmp;
> i:=i+;
> end loop;
>
> For tmp in 0..temp_table.Count-1 loop
>
> ...
> commit;
> end loop;
>
> This is simple for a little script but it's awful to do with bigs
 ones!
>
> Is there a way (i.e. an Oracle setting) that I can use?
>
> I can't add more rollback segment!
>
> I get this error in Oracle 7.4 and Oracle 8.
>
> On the machine running Oracle 8 we have 4 rollback segment 1 G each!
>
> Plase help!
>
>

Inside your loop you are updating the table you are selecting from. Also you are committing in your loop. As Oracle still needs the records you committed to construct a read-consistent image, there's no other solution than to move the commit *outside* the loop. If that means you need to enlarge you rollback segment, there is nothing that can be done about that: just do it.
The other reason for this error is setting optimal too low. As you have rollback segments of 1 G, you're probably not using optimal. You might want to consider to start using that as it will trim back unused rollback space.
If you have rollback segments of 1G however, probably there is more going on in your database. I would try to get a complete picture, instead of taking an insolated measure.

Regards,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Oct 06 2000 - 01:34:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US