Re: Unable to extend rollback segment problem

Hi,

Your rollback tablespace is definitely too small but to me it seems to be because you're updating table 1 unconditionally in a loop (if statement) and the commit is outside you're loop. Either:
- put the commit in the loop
- put the first update outside the loop so the elseif   will contain the loop

Have fun,

Jeroen

Mike Levin wrote:

> 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"
>
> ****************************************************************************
>
> 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 Mon Aug 24 1998 - 00:00:00 CDT