Re: ORA-1555 snapshot to old

JohannesBavendiek_at_gmx.de wrote:
>
> We sometiomes get ORA-1555 snapshot too old. The database has a size of about
> 100 GB. We increased the number of rollback segments to 40. Now they have the
> following storage definition: 1 - 10: (initial 4M next 4M optimal 8M
> minextents 2 maxextents 250) 11 - 40: (initial 1M next 4M optimal 8M
> minextents 2 maxextents 250) system: (initial 50K next 1M optimal 8M
> minextents 2 maxextents 300). PSAPROLL was increased to 1400 MB. We sometimes
> get ORA-1555 in a job, running only 20 minutes. We do NOT get ORA-1628 (max
> extents reached) or ORA-1562 (TS full). The job selects only about 3600 rows.
> This job runs every workday at the same time, the error occcurs only
> sometimes. The error occurs since we implemented HR. The job starts (and
> ends) while the full online backup is running. Has anyone an idea, what we
> can do? If you answer, please notify me by e-mail (JohannesBavendiek_at_gmx.de)
>

You should have all rollback segments have the same storage parameters since
you can not control who is on which rollback segment.

What is more important is that minextents should be increased. Snapshot too old indicates that data that has allready been commmited before was overwritten. This means that a wrap arround has occurred. If you increase minextents this wrap arround will take place less frequently.
And therefore the chance that commited data that is needed by a 'long' running query will be over written is less likely.

• Standard disclaimer applies --