Re: ORA - 01555 rollback segment too old

Oops

Oracle veriosn is 8174 on solaris, the largest table is 20GB but the issue only started to happen when we introduce partition tables... does this mean that partition will take more of the RBS's then other's? also this job which fails drops and then re creates a partition, which means creating the DDL.. also includes a procedure, we tried to hard code a RBS but there are 5 jobs which depende on it... too complicated. Oracle support told me to keep trying the sizes of the rollback's, I did this select query, but I don't know how to get the " If Shrinks "Low" and Avesize "High" then, Optsize is OK" from the select query. I don't know what I'm missing on this query

"Select a.name, b.extents, b.rssize, b.optsize, b.shrinks, b.aveshrink, b.aveactive, b.wraps, b.extends, b.status from v$rollname a, v$rollstat b
where a.usn = b.usn;"

If Shrinks "High" and Avesize "High" then, Optsize = too low, increase optimal
If Shrinks "High" and Avesize "Low" then, Optsize = too low, increase optimal
If Shrinks "Low" and Avesize "Low" then, Optsize = Too high, reduce optimal (unless nearly equal to AVEACTIVE) If Shrinks "Low" and Avesize "High" then, Optsize is OK

Thanks
Maria

"Eric Parker" <eric.parker.spamless_at_virgin.net> wrote in message news:<DvPOa.5712$ju6.102614_at_newsfep4-glfd.server.ntli.net>...
> "Maria" <evoradba_at_yahoo.ca> wrote in message
> news:351fd9d6.0307081752.72ea3340_at_posting.google.com...
> > Hi all
> >
> > I have a database which is about 500GB in size, a tablespace for the
> > ROLLBACK which is 40GG with 20 rollback @ 780MB each, then we
> > introduce partition tables after about 2 weeks we start getting
> > "ORA-01555 rollback segment RBS1 too old, too small" I called oracle
> > support on this they are useless, they are just giving me notes to
> > read, which most of them do not apply (I have a data warehouse db). I
> > don't know what to do anymore? currently we are planning to hard code
> > a rollback to this job which fails, which drop and recreates a
> > partition table with 2 partitions, is there any issues with rollback
> > segments with partition tables? should I increase the rollback's from
> > 20 to 30 rollback segments? any ideas would be great, I'm running out
> > of ideas as what to do next
> > Thanks
> > Maria
>
> Maria
>
> 1555 errors are commonly caused by issuing commits while a cursor is left
> open.
> Subsequent fetches are then in danger of receiving a 1555.
> If this is your case rewriting the code to close and re-open the cursor at
> each commit
> will probably solve it. I realise this may be difficult but it ensures that
> the cursor
> is based on the actual state of the database and not a snapshot that may be
> trampled
> on at any moment.
>
> HTH
>
> eric
Received on Wed Jul 09 2003 - 20:36:59 CDT