Re: Problem with SET TRANSACTION USE ROLLBACK SEGMENT

• On 31 Dec 2004 06:27:32 -0800, fitzjarrell_at_cox.net wrote:

> Dave wrote:

>> "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>

>> >
>> >>|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

>> >>segment and told Oracle to use it:
>> >>
>> >>|SET TRANSACTION USE ROLLBACK SEGMENT RBSBIG1;
>> >>|@/export/home/oracle/scripts/stats_gather.sql;

[...]

> The OP is using PL/SQL to loop through the table names, and, as such,
> cannot issue such direcives from within a PL/SQL loop. This was
> clearly stated in the original text; this is , therefore, not an
> availanle option. The only other option the OP has is to ensure that
> the ONLY rollback segment online is RBSBIG1 when this script is run, an
> option I do not suggest be used. Sybrand has already indicated that,
> outside of using gather_schema_stats in place of gather_table_stats,
> the only VIABLE option is to resize the remaining rollback segments to
> match that of RBSBIG1.

>
> Personally, I prefer using dbms_stats.gather_schema_stats().

Because I have to exclude some tables I think I'll solve the problem with an "outside"-loop in a shell- or a Perl-script.

Thanks to all. Received on Fri Dec 31 2004 - 09:55:30 CST