Problem with SET TRANSACTION USE ROLLBACK SEGMENT

From: Holger Marzen <holger_at_marzen.de>
Date: 31 Dec 2004 08:38:45 GMT
Message-ID: <cr336l$lsb$1@bluebell.marzen.de>

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? Received on Fri Dec 31 2004 - 02:38:45 CST