Re: Problem with SET TRANSACTION USE ROLLBACK SEGMENT

Holger Marzen wrote:

> Oracle 8i / Solaris SPARC
>
> We gather statistics about the tables with a pl/sql script that looks
> like
>
> |for lrec_Table in lcur_Tables
> |loop
> | dbms_stats.gather_table_stats(...)
> |end loop;
>
> But sometimes it crashed, probably due to heavy activity of the
> database (ORA-01555: snapshot too old). So I created a very big rollback
> segment and told Oracle to use it:
>
> |SET TRANSACTION USE ROLLBACK SEGMENT RBSBIG1;
> |@/export/home/oracle/scripts/stats_gather.sql;
>
> What I don't understand is the fact that it still crashes sometimes with
>
> |ORA-01555: snapshot too old: rollback segment number 29 with name
> |"RBS17" too small
>
> Why does Oracle use RBS17 (a small one) and not RBSBIG1? RBSBIG1 is
> definitvely online.
>
> Any ideas?

SET TRANSACTION is generally a bad solution to any problem. There is, for one thing, nothing to prevent another transaction from grabbing that segment first.

Resize all segemnts or, better yet, upgrade to a currently supported version of Oracle (8i has less than 12 hours left in my timezone) and use UNDO.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)