Re: ORA-15555 snapshot too old?

Kevin Johnson Serco DPE/IO wrote:
>
> Grandjanny Jerome (LDG) wrote:
> > > Friedel Hosenfeld <friedel_at_pz-oekosys.uni-kiel.d400.de> writes:
> > > >Since some days ago the database export doesn't work,
> > > >giving instead the message "ORA-01555: snapshot too old (rollback s
> > > >segment too small)".
> > <snip>
> > We also have the same problem sometimes with a database of these
> > dimensions :
> > - 7 Gigs database / About 4 Gigs currently filled,
> > - db_block_size = 8 K
> > - Rollback segment : Initial 5 Megs, Next 10 Megs, Max Extents 505,
> > Optimal 5 Megs
> > So, theoretically, the rollback segment can increase up to 8 K * 505
> > = About 4 Gigs.
> > But we have the ORA-01555 error. Using sqldba and monitoring rollback,
> > I see my rollback segment with current size = 42 Megs and highwater = 92
> > Megs meaning (if I understand highwater) that the maximun size of the
> > rollback segment was 92 Megs.
> > So why couldn't it dynamically increase more than 92 Megs ?
>
> I don't know, but I expereienced exactly this, i.e.
> - plenty of space for rollback segment growth
> - monitoring of rollback segments showed RBSs remained small
> - ORA-01555 error.
>
> We tried different sizing of rollback segments to no avail.
> In the end the developer re-coded the program !
>
> I never got a satisfactory answer this.
>
> Kevin.
>
> --
> ______________________________________________________________________________
> | / _ o _ | _ | _ _ _ _ _ EMAIL :
 johnson_at_mail.esrin.esa.it
> || /_|\ /||/ | |/ \|/ ||/ ||_ / \|/ | PHONE : (+39) 6 941 80 633
> | \\__ \/ || | \_/\_/| || |__|\_/| | ADDRESS : European Space Agency
> Via Galileo Galilei
> Information Administrator Frascati 00044
> ITALIA.
Might be your configuration. From all of your texts, I can't determine if you have seperate tablespaces for rollback segments and temp-space. If not, that might be your problem. Any action on the database uses rollback segments and temp-space, so if you have only one tablespace for data, rollback segments and temp-space, it may seem as if you have enough space left, but the transaction might take away a lot of temp-space.

If this is not the problem, let me know (include some more info on your config), I'll think it over again..

Roald.

-- 

+-------------------------------------------------------+
| R.A. van Geleuken                     roald_at_xs4all.nl |
+-------------------------------------------------------+
| There's no such thing as impossibility, only          |
| extremely low chance                                  |
+-------------------------------------------------------+