Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Mass modification of the data of a column

Mass modification of the data of a column

From: Robert <rober_at_sansadresse.com>
Date: Mon, 24 Jan 2005 11:56:40 +0100
Message-ID: <41f4d468$0$26222$626a14ce@news.free.fr>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US