Re: Fetch to copy tables by block

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 17 Sep 1999 08:44:29 +0100
Message-ID: <937554659.6546.0.nnrp-02.9e984b29@news.demon.co.uk>

Others have already come up with the best approach for 8.0.5:

         either use the COPY command with copycommit set
or      make the table nologging and use the /*+ append */ hint

In both cases this copies all data in the source in one command. How are you planning to avoid other users changing the data whilst you do the copy though ? The insert /*+ append */ will allow you to lock the source table exclusively before you start.

If there is some reason why you don't want to use either of these options, then you could use a PL/SQL loop combined with dbms_sql to use array fetches and array inserts, committing after each insert - see my web-site PL/SQL -> arrays in PL/SQL for a simple example.

Another alternative is to break the source table into rowid ranges, and then copy each rowid range in turn. Again my web-site gives an indication, but only for version 7. The concept is the same for version 8, but it is easier to generate the rowid ranges.

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Received on Fri Sep 17 1999 - 02:44:29 CDT