Re: Problem with SET TRANSACTION USE ROLLBACK SEGMENT

"Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message news:i17at0h323jgf357nf98nbrchu0p537hbs_at_4ax.com...
> 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

or set the rollback segment before each call to dbms_stats Received on Fri Dec 31 2004 - 05:21:16 CST