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

First of all you should not use the OPTIMAL clause on rollback segments, however this is probably not the cause of the ORA-1555 error. If you are doing a bulk load of data (ie. lots of inserts) I would suggest you try the following:

CREATE TABLESPACE rbs1 ....
CREATE ROLLBACK SEGMENT bigrbs TABLESPACE rbs1;

Then whenever you need to load your data do:

ALTER ROLLBACK SEGMENT bigrbs ONLINE;
SET TRANSACTION USE ROLLBACK SEGMENT bigrbs; Do inserts
ALTER ROLLBACK SEGMENT bigrbs OFFLINE;

Note that if you COMMIT (either explicitely by issuing a COMMIT statement, or implicitely by doing DDL, ALTER SESSION, etc.) then you will have to "SET TRANSACTION USE ROLLBACK SEGMENT bigrbs" again!

HTH,
Gollum

"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 Tue Feb 06 2001 - 15:21:54 CST