Re: Rollback Segment and how to use the statement "Set transaction use rollback segment ???"

Hi Michael,

Your problem is probably that there are running other transactions on Your database which make changes on the table You want to analyze. If this other transactions make changes to datablocks Your transaction have to read, Your transaction have to read the befor image in an other rollback segment which belongs to that other transaction. If one of these block is overwritten by a third transaction, You'll get the ORA-1555 error. The third transaction is only able to overwrite the second transactions bevor images when the second transaction does a commit. To solve Your problem You can do different things. First You can do the analyze job when there are only few other transactions running (especially no transactions changing episodes) Second You can increase all rollback segments. But if You increase the size of the rollback segments, do not only change the maximum size. To solve Your Problem You have to increase the minimum size.

--
Best Regards,

Ralph Ganszky

• The above opinions are mine and do not represent any official
• standpoints of my employer or third party organizations.

Wind wrote:

> Hi guys,
>
> I am trying to use sql statement "set transaction use rollback
> segment bigroll" to set that Oracle will set the "bigroll" (a much
> bigger rollback segment in size) to do the analyze on some tables.
> However, I got this error message:
>
> analyze table episodes estimate statistics sample 50 percent
> *
> ERROR at line 1:
> ORA-01555: snapshot too old: rollback segment number 21 with name
> "R20" too small
>
> while the sql statements used are liike this:
>
> set transaction use rollback segment bigroll;
> analyze table episodes estimate statistics sample 50 percent;
>
> but "bigroll" is not R20. How did that happen? Can someone explain
> to me how rollback segments work?
>
> please either post your reply here or send email to me using
> mleung_at_earthlink.net or mleung_at_intelus.com
>
> thanks in advance
>
> Michael Leung
>
> WindyCloud
> 7k_at_gamestats.com
>
> Realms of the Kingdoms, http://7k.gamestats.com
Received on Sun Aug 29 1999 - 06:39:03 CDT