Re: ORA-01555: snapshot too old (rollback segment too small)

Your extent sizes seem a bit small, you're using an optimal clause which is always a bad idea, and creating rollback segments with just 2 extents is a recipe for disaster.

Get rid of optimal, make your extents bigger, use locally managed tablespaces (won't help this problem, but is a good idea anyway), and create them with a minextents of 6 or more. (The standard Oracle recommendation is 20, but that's just daft nonsense regurgitated without correction since Oracle 6 days).

Regards
HJR "Aaron D. Hager" <adhager_at_onebox.com> wrote in message news:YxZf6.9210$5H.2140973_at_typhoon2.ba-dsg.net...
> I am getting occasional ORA-01555 errors. It has happened when I insert
 data
> or create tables.
> My rollback segments were created as below. (I originally had only 4 but
> added more in an attempt to solve this issue.)
>
> create tablespace rbs datafile
> '/ora05/app/oracle/product/8.1.5/database/rbs01.dbf' size 15M reuse
> default storage (
> initial 512k
> next 512k
> pctincrease 0
> minextents 2
> maxextents unlimited
> );
> alter tablespace rbs add datafile
> '/ora05/app/oracle/product/8.1.5/database/rbs02.dat' size 15M autoextend
 on;
> create rollback segment r01 tablespace rbs storage( optimal 2200K );
> create rollback segment r02 tablespace rbs storage( optimal 2200K );
> create rollback segment r03 tablespace rbs storage(optimal 2200K );
> create rollback segment r04 tablespace rbs storage( optimal 2200K );
> create rollback segment r05 tablespace rbs storage( optimal 2200K );
> create rollback segment r06 tablespace rbs storage( optimal 2200K );
> create rollback segment r07 tablespace rbs storage( optimal 2200K );
> alter rollback segment r01 online;
> alter rollback segment r02 online;
> alter rollback segment r03 online;
> alter rollback segment r04 online;
> alter rollback segment r05 online;
> alter rollback segment r06 online;
> alter rollback segment r07 online;
>
>
>
Received on Wed Feb 07 2001 - 02:03:37 CST