Re: ORA-01595

Increasing the optimal size as was suggested at the "*action" part did not work well.

I will try to not use optimal and see.

Thank you for your prompt answer.

Ofer

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3fcb0acd$0$13984$afc38c87_at_news.optusnet.com.au...
>
> "Ofer" <oferr_at_realcommerce.co.il> wrote in message
> news:bqev0e$kfp$1_at_news2.netvision.net.il...
> > I have a non-active DB (8.1.7), and every day I get the following error:
> >
> > ORA-01595: error freeing extent (1) of rollback segment (19))
> > ORA-01594: attempt to wrap into rollback segment (19) extent (1) which
is
> > being freed
> >
> > Any idea how to resolve it?
>
> Well, the full text of error message 1594 says it all really:
>
> Cause: Undo generated to free a rollback segment extent is attempting to
> write into the same extent due to small extents and/or there are too many
> extents to free.
>
> Action: The rollback segment shrinking will be rolled back by the system;
> increase the optimal size of the rollback segment.
>
> I disagree with the suggested 'Action' though. The simple answer is not
use
> optimal in the first place (and not using optimal is a generally good idea
> for other reasons, which have been discussed at length here before).
>
> The trouble is that when optimal is set, SMON occasionally tries to shrink
> the rollback segment (by dropping extents). But the drop itself causes
> rollback. And your rollback segment is so small that the rollback needed
to
> record the extent drop needs to be written into the extent that's actually
> being dropped in the first place. So a bigger optimal would help. So would
> more extents. So would bigger extents (all 3 are just different ways of
> getting bigger rollback segments). So would not setting optimal in the
first
> place. And, conceivably -though never tested- so would having more
rollback
> segments in the first place.
>
> Regards
> HJR
>
>
Received on Mon Dec 01 2003 - 04:51:45 CST