Re: Rollback segment error - in SELECT statement ???

I can't help for the select getting rollback segment error never heard of this
but regarding the use of a huge rollback segment you can leave it online and use the following command to force oracle to use it for a transaction

set transaction use rollback segment rbs_big;

whatever (insert/delete/update)

commit;

Unless you do this oracle choose a rollback segment for your transaction and you are never sure that it will be the huge one.

Hope this help,

Chetan Wagle <cwagle_at_ctp.com> wrote in article <7s7qfu$olh$1_at_herald.ctp.com>...
> Hi everybody,
>
> I am very clear about the functioning of rollback segments and the
> conditions in which you get errors in rollback segments. As we all know,
> rollback segments store the before and after images of data blocks.
>
> I have three tables that have a very large number of records :
typically
> in the range of 2.5 to 2.8 million records per table since I work on a
> billing application. I have medium sized rollback segments and I have
> created one huge rollback segment which I bring online whenever I run my
> billing.
>
> In one of my tables, I have a column named status. Each record
> corresponds to one sale. Before I run billing, the status of the records
is
> 'U' meaning unbilled. As the tariff amount calcluation (a stored
procedure)
> runs along, after every 100 records, it commits and changes the status to
> 'B' meaning Billed successfully or 'P' meaning problem in billing.
>
> When billing is not running, I can do all operations without any
> problems. However, when I run billing (which typically takes a number of
> hours), I check the count(*) where status='U' to find out how many
records
> are still remaining to be processed and this tells me the progress.
>
> A strange thing that I have noticed is that when I fire this select
> query when the billing is running, I get rollback segment errors. as per
> oracle, rollback segments are not used unless you perform some data
> manipulation - oracle says that a rollback segment gets allocated only
when
> the first insert/update/delete statement comes along.
>
> Then why do I get this error when I fire a select ? The billing
procedure
> takes a lot of the db buffers and constantly flushes data in and out. the
> machine also slows down considerably when billing is on. I have Oracle
7.3
> on a Sun UltraSPARC-2 machine running Solaris 2.6.
>
>
> Any ideas anyone ???
>
> TIA,
> Chetan
>
>
>
>
>
>
Received on Tue Sep 21 1999 - 06:55:38 CDT