RE: COPY COMMAND in SQLPLUS

It is not as simple as having a parallel degree switch, but something similar can be done.  

The first thing to do is ascertain the available bandwidth you are willing to consume between the database servers (if they are remote from each other, and I'm not sure why you wouldn't use transportable tablespaces if they are near each other.)  

You'll want the product of arraysize and copycommit somewhere in the range 512 to 8192 as of the last time I measured that was a wide plateau of performance equality.  

Now if your data is in multiple tables, simply run as many parallel sqlplus sessions as makes sense. If your destination tablespaces are on separate disk farm stripesets, arrange for that parallelism as well, if the tables are occupants of different tablespaces on different stripesets.  

If you have at least one table that is much bigger than the other tables, figure out a reasonable partitioning by some unique index and run multiple copies from that table to multiple separate tables (again with the destinations separated if possible and likely that destination disk speed enters into likely wait time in a significant way.) Then if you have partitioning you can simply swap in tables as partitions, or if not select in the data from all but one of the copy pieces (the destination) from the local database.  

Good luck. Remember to set long at least as long as the length of your longest actual long - copy silently truncates longs longer than what you specify (80 is the default if I recall correctly).  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anurag Verma
Sent: Monday, June 16, 2008 2:39 PM
To: ORACLE-L
Subject: COPY COMMAND in SQLPLUS    

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 




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