Re: COPY COMMAND in SQLPLUS

Anurag,

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.

Toon

On 6/16/08, Anurag Verma <anuragdba_at_gmail.com> 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
+31-615907269
toon_at_rulegen_dot_com
www_dot_rulegen_dot_com

Author: "Applied Mathematics for Database Professionals"

--
http://www.freelists.org/webpage/oracle-l