Since you're updating every row in table1 and you aren't doing a commit
until all rows are updated, your rollback segment essentially has to be
large enough to contain all rows in table1. Using your estimates, that
amounts to about 1.2 gigabytes. The other option is to change your code and
do periodic commits.
Winnie Liu wrote in message <6rqrih$fus_at_sjx-ixn1.ix.netcom.com>...
>If you get the error saying the Oracle is unable to extend rollback
segment,
>it is time for you to add another datafile to the rollback segment
>tablespace because there is not enough free space for Oracle to get another
>chunk of free data block for the next extent.
>
>Winnie
>
>Mike Levin wrote in message <6rph12$3qt12_at_kirk.tinet.ie>...
>>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 Tue Aug 25 1998 - 00:00:00 CDT
