RE: COPY COMMAND in SQLPLUS
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <anuragdba@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Jun 2008 16:21:08 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@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
Other related posts:
