Re: failed to extend rollback segment

From: <markp7832_at_my-deja.com>
Date: Mon, 13 Dec 1999 14:48:56 GMT
Message-ID: <83310m$7h5$1@nnrp1.deja.com>

In article <832vvo$6tu$1_at_nnrp1.deja.com>,   rkegel_at_my-deja.com wrote:
> Hi,
>
> I ran several insert and update statements on large tables (~100'000
> records) using Oracle on Win NT and
> encountered the problem of too small rollback segements for some of
> these statements, e.g.:
>
> ORA-01562: failed to extend rollback segment number 3
> ORA-01650: unable to extend rollback segment RS3 by 512 in tablespace
> RBS
>
> or
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01562: failed to extend rollback segment number 5
> ORA-01650: unable to extend rollback segment RLARGE by 1280 in
> tablespace RBS
>
> The strange thing is that e.g. RS3 has an initial size of 2'048 K with
> a max. number of extents
> of 249, and RLARGE has an initial size of 5'120 K with the same number
> of extents.
>
> The whole tablespace has an actual size of 60 MB and may be extended
to
> 120 MB.
>
> How can it be that updating a table of the size of some Megabytes
needs
> a rollback segment larger than
> 0.5 GB? (2'048 K x 249)
>
> What is going on? Are these error messages misleading? Or am I missing
> something else?
>
> I know that I can specifically set a rollback segment for a certain
> statement with
>
> set transaction use rollback segment xyz
>
> but I am not sure whether it makes any sense to further increase any
of
> these rollback segments and
> retry the statements...
>

.........
> Rainer Kegel
> http://www.scheuring.ch
>

The messages appear to be telling you that you ran out of free space within your rbs tablespace to allocate extents from. You should map you tablespace and look at its usage.

You can use the 'alter tablespace x coalesce;' command to make the rbs segments give up unused extents beyond the optimal setting which I hope you set. You listed your rbs initial setting at 2M but you did not list your next setting. As a general rule for rbs segments your storage settings should be like:

initial    = next
optimal    = inextents (10 - 20) X next
maxextents = number of bytes necessary to support the largest

transaction that you want your system to be able to support without having to commit plus room for the number of average size transactions that are likely to be assigned to the same segment while the large transaction is running.

Also remember that your updates also affect all indexes on the table and those also take rbs extent space.

I hope this helps.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 08:48:56 CST