| 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
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.
>
> Anyone have any ideas as to what I'm missing here?
> --
> --------------------------------------------------------------------------
 |
 |