i have to copy data from some tables to some backup tables on an Oracle 9i server. some tables have more than one million of records.
does anybody have any hint to perform a fast and secure copy?
bulk copy for example, how does it work?
Originally posted by AlanP
try create table as select... with the nologging option.
Ok, but the problem is i have to perform some operations during the copy. one of the field has to be substituted by its transcode value (got by a join on another table).
do you think the following can work?
CREATE OR REPLACE PROCEDURE nrows_at_a_time (p_array_size IN PLS_INTEGER DEFAULT 100)
TYPE ARRAY IS TABLE OF table_origin%ROWTYPE;
CURSOR c IS SELECT * FROM table_origin;
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO table_target VALUES (l_data.field1(i), l_data.field2(i), transcodeValueOf(l_data.field3(i)) );
EXIT WHEN c%NOTFOUND;
or do you think it's better to use "create table as select..." with the nologging option and then to update field3 of the created table with the new value (coming from a lookup table)?