Re: ORA-01595

"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 - 03:32:54 CST