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

Chetan Wagle a écrit dans le message <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.
>

Rollback segments store only before image.

> 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.

It's not so strange.
The billing modified some rows so rollback segments are used and Select statements fired use these rollback segments to get the data and not the data files.
Then you can have some errors like "snapshot too old" because of the high frequency of the commits.

>
> 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 - 07:01:59 CDT