cannot extend rollback segment error

[julian]

Originally posted by pando
nologging is only useful for direct insert/CTAS operations

That is true. You can create a "nologging" table by specifying NOLOGGING when you create the table with a given subquery in the CREATE TABLE ... AS SELECT statement.

Remember though that after the statement is completed, all future statements are logged/fully recoverable.

Also, using Create-Table-As-Select (= CTAS) with nologging, undo for dictionary operations is generated and logged with minimal redo.

To add something more: only the following operations can make use of the NOLOGGING option:

alter table ... move partition
alter table ... split partition
alter index ... split partition
alter index ... rebuild
alter index ... rebuild partition
create table ... as select
create index
direct load with SQL*Loader
direct load INSERT

[abhaysk]

Breaking up of the TRAN is easy i belive....

Declare CNT number;
    Counter number;
    
Begin

Cnt :=0;
Counter:=0;

SELECT cOUNT(*) into cnt
FROM   UR_TABLE

cnt := ceil(cnt/200000);(Divide the cnt depending on how much records u have and on ur rollback segment size.)

For Counter in 0 .. cnt Loop

Update statement with this condition
and rownum < 200001;

Commit;
    

End Loop;

End;
/ 

[pando]

with

Update statement with this condition
and rownum < 200001;


you will be updating same rows over and over again

[abhaysk]

Originally posted by pando
with

Update statement with this condition
and rownum < 200001;


you will be updating same rows over and over again

Oops i am sorry......

In our case we use a column as flag....hence it wud be posible then.......

I forgot to mention......

Sorry 1ce more.....this is bad idea......

Abhay.

[stmontgo]

Originally posted by abhaysk
Breaking up of the TRAN is easy i belive....

Declare CNT number;
    Counter number;
    
Begin

Cnt :=0;
Counter:=0;

SELECT cOUNT(*) into cnt
FROM   UR_TABLE

cnt := ceil(cnt/200000);(Divide the cnt depending on how much records u have and on ur rollback segment size.)

For Counter in 0 .. cnt Loop

Update statement with this condition
and rownum < 200001;

Commit;
    

End Loop;

End;
/ 

multiple committs increase the chance of ORA-1555 significantly

[eldoskaria]

I hope whatever you have to update is having a field value which when negated will exclude the updated records. Under such condition u can update the table by giving a where clause as "and rownum < &desired_rownum_w.r.t_ur_m/c_capacity" in each update cycle and multiple update cycle may be used with commit or rollback between each cycle.

[gandolf989]

I also agree that Slim Daves method is a good one. But there is another option incase you can't do Slim Daves method. You can create a table with a done column and a pkey column and use that table to update parts of the records. i.e. If you were doing updates based on states you would have 50 values with a pkey 1-50 select the record with the lowest pkey where done=0 and update the main table based on that record then update the driving table to set done = 1 for that record. It is more work but you can do frequent commits without worrying about ora-1555, and it is also restartable. I do some t-sql programming for SQL Server and that is how I do bulk updates. Just a thought. You would of course want to minimize any other processing that might be running concurrently.