Re: ORA-1555 Snapshots Too Old Error[Q]

In article: <3qnlqa$odo_at_ixnews4.ix.netcom.com> whitep_at_ix.netcom.com (Perry White) writes:
>
> We are running Oracle 7.0 for a PeopleSoft Payroll system which is
> getting larger and larger. A developer keeps getting the ORA-1555
> snapshot too old error when doing a massive delete or truncate of a
> large work table while payroll is being updated. This error indicates
> the rollback segments are too small. I have added 3 additional
> rollbacks to the database to make a total of 6 with no improvement.
> The settings are "initial 1M minextents 2 next 2M optimal 5M" and are
> the same on all 6 of the segments. How can I tune these rollback
> segments to avoid this error? Are there other measures I can take to
> prevent this error? I think I am ok for day to day updates,its just
> when a massive delete occurs we have this error.
>
> e-mail me directly. Thanks in advance for your suggestions.
>

        As someboody has asked for this to be public here goes.

        The snapshot too old message is caused when oracle cannot read the information from the rollback segments needed to construct a read consistent block. Normally because old information has been overwritten.

        The problem is not the number of rollback segments but the size of then. remember that a transaction cannot span more than one rollback segment.

        The optimal clause may also be causing a problem, because when a rollback segment shinks all the old information is lost.

        So

1. set optimal high e.g. 100M
2. re-create rollback segments with larger extents.

        Hope the above helps.

	Come, let us retract the foreskin of ignorance, and apply the
	wirebrush of enlightenment

	Tim London			phone :	+44 1753 662393
	Oracle DBA,Trainer and		mobile:	+44 836 287064
	part time OU tutor		Email : tim_at_artemis.demon.co.uk
----------------------------------------------------------------------------