| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: "snapshot too old" or "cannot extend rollback segment" errors running PL/SQL script
It is interesting that this problem pops up every few weeks. In some form or
another I have encountered it periodically ever since I entered the Oracle
world.
In a way I consider this to be one of the dark corners of oracle. It is a "gotcha" in which the actual error message doesn't really provide sufficient information. Technically the error message is absolutely correct, but a person still ends up thinking "but what the hell does that mean?".
I suspect that Oracle probably could solve this problem and allow read consistancy for the select cursor while allowing database-modifying transactions to be committed - however, there seems to be little interest in making this process easier for developers.
Sybrand Bakker wrote:
> Hi Jamie,
> The problem here is you are committing in the loop.
> The problem is
>
> by releasing the
> > rollback extents into the pool so that no other queries could overwrite my
> > inactive extents.
> Yes, the data is released, but it is still needed for read-consistency, and
> it still is in the rollback segment. As soon as it is overwritten you get
> the 1555 error.
> You need to commit outside the loop.
> IMO, your rollback segments are way too small and you need to increase them.
> In small databases my rollback tablespace is some 128M, with an individual
> rollback segment reaching 32M.
> Calculating with your figures, my conclusion is one of your rollback
> segments alone could reach 30M (249 * 1/8 M).
> This means your tablespace is too small.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Jamie Schrumpf <hatterasNOSPAM_at_hotbot.com> wrote in message
> news:7q1ma3$8rf$4_at_birch.prod.itd.earthlink.net...
> > I'm trying to build a fact table that's got about 85k records. I have 8
> > rollback segments at 125k initial, 125k next, minextents 18 maxextents
> 249, no
> > optimal, in a 33meg tablespace. The script runs in a loop that reads a
> cursor
> > from a data table, loads variables with keys from dimension tables, then
> writes
> > out the fact table record. There are 8 numbers, one date, and a char(1)
> column
> > in the fact table. I do a commit after each loop of the script.
> >
> > I get one of these two errors every time I run this script. I thought
> that
> > doing the commit after each loop would eliminate this problem by releasing
> the
> > rollback extents into the pool so that no other queries could overwrite my
> > inactive extents.
> >
> > The truly scary part is that it usually works for a test table I'm using,
> but
> > not for the production table. All I do is change the name in the script.
 |
 |