Printable View
hi all,
I have a table with about 20million records.
I do a update of all records in a single update statement.
So i get this unable to extend the rollback segment error
adding a datafile will solve the problem i guess.
But is there any way of temporary stopping of writing into rollback segment before updating and resuming it after the update statement.
thanx
Instead of directly updaing 20Milion records once can u not brake ur transaction into many update and commit statement ?Quote:
I do a update of all records in a single update statement.
Regards
Viraj
----------
9i OCA
Hi,
By adding more datafiles you can solve the problem. There is no way to stop writing to rollback segment is you are performing an update.
Regards,
Also you can place your table in nologging mode and later change it to logging mode.
Regards
viraj
-----------
9i OCA
Hi
Create a huge table space, create a rollback segment with initian and next say 500M min extents 5 make the optimal as 2 GB.
and before the update give the command
set transaction rollback segment segment_name;
this will make sure that you use only this rollback segment that you have just created.
Regards
Amar
I think that you would be pleasantly surprised by the benefits of not doing the operation as an update.
You could instead try "CTAS" -- create table ... as select ...
You can make the desired update changes as part of the select, and with the "nologging" option you will generate very little redo log (I don't believe that an update performed against a "nologging" table will cut down on the amount of logging performed).
You can then rename the old table, rename the new table ,apply indexes, constraints, grants etc.
It will be much faster.
There is no such thing as "performing UPDATE in nologging mode", no matter if your table is in logging or nologging mode......Quote:
Originally posted by virajvk
Also you can place your table in nologging mode and later change it to logging mode.
I would do it group by group.
I think that the suggestion above is very good; do follow it.Quote:
Originally posted by slimdave
I think that you would be pleasantly surprised by the benefits of not doing the operation as an update.
You could instead try "CTAS" -- create table ... as select ...
You can make the desired update changes as part of the select, and with the "nologging" option you will generate very little redo log (I don't believe that an update performed against a "nologging" table will cut down on the amount of logging performed).
You can then rename the old table, rename the new table ,apply indexes, constraints, grants etc.
It will be much faster.
Quote:
Originally posted by virajvk
Also you can place your table in nologging mode and later change it to logging mode.
Regards
viraj
-----------
9i OCA
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.Quote:
Originally posted by pando
nologging is only useful for direct insert/CTAS operations
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
Breaking up of the TRAN is easy i belive....
PHP Code: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;
/
with
Update statement with this condition
and rownum < 200001;
you will be updating same rows over and over again
Oops i am sorry......Quote:
Originally posted by pando
with
Update statement with this condition
and rownum < 200001;
you will be updating same rows over and over again
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.
Quote:
Originally posted by abhaysk
Breaking up of the TRAN is easy i belive....
PHP Code: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
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.
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.