From: Toon Koppelaars <>
Date: Mon, 16 Jun 2008 22:05:35 +0200
Message-ID: <>


Yes you can. Copy command just issues the query you supply it with and you can force it to do parallel query with hints (or otherwise). The question however is: will it speed up your copy process? What is the bottleneck? Reading the data from the source-table, or something else? What would you ideally want to be the bottleneck of a large copy-process?

A couple of weeks ago I was challenged to copy a 270 Gbyte table from one database to another. Given the 100 Mbit/second ethernet in between the two databases, this could not be done any faster than a little less than 6 hours. I did some testing and eventually ended up with a copy-process that would have the network as its bottleneck. It turned out that just having the process perform a (non-parallel) full table scan would generate rows at a rate that the network could not keep up with. Point being: forcing it to perform a parallel scan would not speed up any further.

Here's what I did (with thanks going to Alex Gorbachev):

In the database session that performs the 'from/using' query of the copy-command:
alter session set "_serial_direct_read"=true; alter session set db_file_multiblock_read_count=128; -- Increased from 64

The _serial_direct_read prevented me from hitting a 'snapshot too old' error. Apparently (though still not 100% sure) it prevents 'block cleanouts' that cause block writes, which in turn cause rollback segment allocation. I used a database login-trigger to perform these two alter session statements in the session that performs the 'from/using' query.

Then (of course) in SQLPlus I set the arraysize to its maximum possible value:
set arraysize = 5000

The query on the source table was performing a simple full table scan. Copy commit was set to zero: i.e. commit once at the end of the copy process. And finally: no indexes or constraints were present on the destination-table (which was an empty table upon start of the copy-process). The copy process took a little under 6 hours. It was copying from a 9i database to an 11G database. After the copy-process it took another 6-7 hours to build indexes and enable constraints.


On 6/16/08, Anurag Verma <> wrote:

> Hi,
> Can we use parallel degree option to copy a table from one database to
> another?
> I have 2 Oracle 9i databases and have to copy some huge amount of data.
> Thinking of various options and want to know whether I can implement
> parallel option (in the same way what we do with Parallel DMLs).
> This is what the COPY syntax I am seeing from sqlplus utility.
> usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
> <db> : database string, e.g., hr/your_password_at_d:chicago-mktg
> <opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
> <table>: name of the destination table
> <cols> : a comma-separated list of destination column aliases
> <sel> : any valid SQL SELECT statement
> --
> Thanks,
> Anurag Verma,
> Database Administrator
> ERCOT(Electric Reliability Council of Texas),
> Texas 76574

Toon Koppelaars
RuleGen BV

Author: "Applied Mathematics for Database Professionals"

Received on Mon Jun 16 2008 - 15:05:35 CDT

Original text of this message