cannot extend rollback segment error

[sona]

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

[virajvk]

I do a update of all records in a single update statement.

Instead of directly updaing 20Milion records once can u not brake ur transaction into many update and commit statement ?

Regards
Viraj
----------
9i OCA

[sursujmad]

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,

[virajvk]

Also you can place your table in nologging mode and later change it to logging mode.

Regards
viraj
-----------
9i OCA

[adewri]

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

[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.

[jmodic]

Originally posted by virajvk
Also you can place your table in nologging mode and later change it to logging mode.

There is no such thing as "performing UPDATE in nologging mode", no matter if your table is in logging or nologging mode......

[agasimani]

I would do it group by group.

[julian]

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.

I think that the suggestion above is very good; do follow it.

[pando]

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