Re: pl/sql problem

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 Mon Jul 12 1999 - 15:25:34 CDT