Re: ORA - 01555 rollback segment too old

"Maria" <evoradba_at_yahoo.ca> wrote in message news:351fd9d6.0307091736.3b130fab_at_posting.google.com...
> 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

Maria

As far as I'm concerned the no. 1 cause of this is fetchin from a cursor after a commit when
that cursor was opened before the commit. You may be able to find a "solution" by adjusting rollback segments but you also
may find that your problem re-appears next week when there is a change to the
data or user load.

Can you verify that you are not using cursors and commits (might be DDL operation)
as I've descibed ?

Regards

eric Received on Thu Jul 10 2003 - 04:47:05 CDT