-
cannot extend rollback segment error
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
-
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
A Wise Man Knows How much he doesn't know !!!
-
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
A Wise Man Knows How much he doesn't know !!!
-
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
Amar
"There is a difference between knowing the path and walking the path."
-
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.
-
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......
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
I would do it group by group.
Agasimani
OCP(10g/9i/8i/8)
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|