Re: Problem with SET TRANSACTION USE ROLLBACK SEGMENT

On 31 Dec 2004 08:38:45 GMT, Holger Marzen <holger_at_marzen.de> 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?

The logical explanation for this is each call to any dbms_stats procedure is probably considered a transaction. Which means you acquire the correct rollback segment for the first table, and for the other tables revert to the usual round robin mechanism.
So either you should:
- use gather_schema_stats
or
- set all rollback segments equally big
(Yes I know, I was forced to do this for export)

--
Sybrand Bakker, Senior Oracle DBA