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

Hi Chetan,

Rollback segment are used by Oracle to insure the read consitency in statement level,
i.e. the data remain unchanged from start to end of a select query even if update/commit occured during this query. However, if many commit occurs during a long query running, it's possible to have an error (snapshot too old) because the data needed by the select are not up to date any more (the size of rollback segment are not unlimited) and Oracle cannot any more maintain the read consistency (statement level). To solve this problem, you can decrease the frequency of commit, or add bigger rollback segments.

Have a nice day

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.
>
> 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 - 07:53:40 CDT