Unable to extend rollback segment problem

From: Mike Levin <mike.levin_at_usa.net>
Date: 1998/08/23
Message-ID: <6rph12$3qt12_at_kirk.tinet.ie>#1/1

[Quoted] [Quoted] I am having problems running this SQL script. The script basically opens 3 cursors, updating the first table's percent column by calculating the percent based on quotient of table 2's amount divided by table 3's amount
where the rows join.

I've run this SQL with no users on the system.

The error I get is
"ORA-01562 Failed to extend rollback segment (id = 1)" "ORA-01650 Unable to extend rollback segment R01 by 1280 in tablespace RBS"

---

[Quoted] [Quoted] set transaction use rollback segment R01

• R01 has the following parameters:
• init extent 199,229,440 bytes
• next extent 5,242,880
• pct increase 0
• max extents 249

curser cur1 is
select table1_key

       fk
       percent

from table1
cur1_row cur1row%rowtype

• table1 has about 2 million rows of about 600 bytes/row

curser cur2 is
select fk_table1_key

       fk
       sum (amount)

from table2
where table1.fk = table2.fk
and table1.key = table2.fk__table1_key group by fk_table1_key

         fk
cur2_row cur2row%rowtype

• table2 has about 3 million rows

curser cur3 is
select fk_table1_key

       fk
       sum (amount)

from table2
where table1.fk = table3.fk
and table1.key = table3.fk__table1_key group by fk_table1_key

         fk
cur3_row cur3row%rowtype

• table3 has about 90,000 rows

begin
open cur1
loop

 fetch cur1 into cur1_row
 exit when cur1%not_found

 open cur2
 fetch cur2 into cur2_row
 open cur3
 fetch cur3 into cur3_row
 if cur2%found
 and cur3%found then

  if table2.amount = 0 then
  update table1
  set percent = 0;
   elsif table2.amount <> 0 then
  update table1
  set percent = (table3.amount / table2.amount)   where table1_key = cur1_row.table1_key   and fk = cur1_row.fk;
  endif;

 endif;

        close cur2;
        close cur3;

end loop;
commit;
close cur1;
end; Received on Sun Aug 23 1998 - 00:00:00 CEST