Re: pl/sql problem

While we're on the subject, how does a rollback segment get fragmented exactly?
I thought it was essentially a ring buffer that extents in the event that the next extent
is occupied with an active transaction. Once transactions are over, it should clear
itself up. I ask this question because one time I had a rollback segment that had extended
quite a bit, and I figured so what, so it's a litter bigger, I got a failed to extend message, tried shriking it, and behaved better after I shrunk it (no error), which makes me wonder why this extra space appeared to be trash, almost like tablespace fragments. Can you explain? I don't see what tablespace fragmentation has in common with rollback fragmentation assuming they're similar. Come to think of it, I guess I'm probably allowed to coalese a rollback segment tablespace, so is the fragmentation the same thing?
I can see how rollback writes depending on the type of transaction could vary in size
but will transactions actually be looking for "free space" pieces? That seems to contratict the tuning ideal that a rollback segment can be the same size with no shrinks or extends
in an ideal enivronment. That doesn't get fragmented..so...?? Sorry for droning on about
this..this one's been in the back of my mind for a couple of months...

• Dc.

markp7832_at_my-deja.com wrote:

> Mark, The error messages are pretty clear. You have a rollback segment
> problem. Either you are inserting more data than your rollback
> segments are designed to handle, or your rollback segment tablespace is
> bably fragmented and needs cleaning up, or similiar.
>
> What do your rollback segments look like. There are several recent
> threads on rollback segments in the newsgroup. (There are almost always
> threads on rollback segments.) In general all your rollback segments
> should be created the same with initial = next, and a minextents of 10
> to 20 extents. You should have at least four segments. The total
> amount of space, extent size X max_extents, must be as large as the
> largest amount of data you will update with only one commit. You may
> also need to allocate a special large segment for maintenance and/or
> some of your batch.
>
> You probably need to re-create your rollback segments with a larger
> extent size.
>
> I hope this helps. Please repost with your rollback information if
> this doesn't. Someone will answer you.
>
> In article <HQqi3.40$M84.792305680_at_mick.cybertrails.com>,
> "Mark Wagner" <mark_at_cybertrails.com> wrote:
> > Im trying to run this pl/sql script and getting the following
> error......
> >
> > SQL> @test2
> > DECLARE
> > *
> > ERROR at line 1:
> > ORA-01562: failed to extend rollback segment number 2
> > ORA-01650: unable to extend rollback segment R01 by 64 in tablespace
> RBS
> > ORA-06512: at line 21
> >
> > here is my pl/sql script.................................
> >
> > declare
> > namel varchar2(30);
> > namef varchar2(30);
> > idcustomer varchar2(40);
> > numacct varchar2(20);
> > cursor test_cursor is
> > select ispCustomer.LastName as LastName, ispCustomer.FirstName as
> > FirstName, ispCustomer.CustomerID as CustomerID,
> > ispbilling.bankaccountnumber as acctnum
> > where ispbilling.customerkey = ispcustomer.customerkey;
> > test_val test_cursor%ROWTYPE;
> > begin
> > open test_cursor;
> > loop
> > fetch test_cursor into test_val;
> > exit when test_cursor%NOTFOUND;
> > namel := test_val.LastName;
> > namef := test_val.FirstName;
> > idcustomer := test_val.CustomerID;
> > numacct := test_val.acctnum;
> > dbms_output.put_line('this is the last name '||TO_CHAR(namel));
> > update deductees
> > set LASTNAME = (select ispcustomer.LastName
> > from ispcustomer, ispbilling
> > where ispbilling.bankaccountnumber = numacct AND
> > ispbilling.customerkey = ispcustomer.customerkey),
> > FIRSTNAME = (select ispcustomer.FirstName
> > from ispcustomer, ispbilling
> > where ispbilling.bankaccountnumber = numacct AND
> > ispbilling.customerkey = ispcustomer.customerkey),
> > CUSTOMERID = (select ispcustomer.CustomerID
> > from ispcustomer, ispbilling
> > where ispbilling.bankaccountnumber = numacct AND
> > ispbilling.customerkey = ispcustomer.customerkey);
> > end loop;
> > close test_cursor;
> > end;
> > .
> > /
> >
> >
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Jul 14 1999 - 23:09:26 CDT