Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Mass modification of the data of a column
Hi all,
I would like to modify the content of a column in a large table.
The column contains two distinct values : AS or AR and each value must be changed to OW (for AS) and RT (for AR).
The table contains about 20 000 000 rows.
I tried to modify the values with a PL/SQL procedure that did nearly this :
LOOP
update tablename set col='OW' where col='AS' and rownum < 50000;
IF (SQL%ROWCOUNT = 0) then
commit;
exit; end if; commit; END LOOP;
If I choose a bigger value for the rownum clause, I usually raise the
"rollback segment to small" error.
So the procedure is very long to complete (hours or days).
So, I am looking for a quicker way to make these mass modifications...
I has the idea to export the table content to a file, change the values then re-import the file. Will it be quicker ? safe ?
Is there a way to execute the update statement without using the rollback segment ?
Any other ideas ?
Thanks a lot in advance,
Bobby Received on Mon Jan 24 2005 - 04:56:40 CST