RE: "Best" way to copy schema data to another DB
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 12 Apr 2010 14:13:14 -0500 (CDT)
Hi Michael,
> I have used datapump most of the time.
>
> cat expdp.par
> DIRECTORY=data_pump_dir
> SCHEMAS=expschema
> DUMPFILE=expschema.dmp
> METRICS=y
> LOGFILE=expschema.log
> EXCLUDE=statistics
>
> cat impdp.par
> DIRECTORY=data_pump_dir
> DUMPFILE=expschema.dmp
> LOGFILE=impdp_newschema.log
> METRICS=y
> SCHEMAS=expschema
> REMAP_SCHEMA=expschema:newschema
> REMAP_TABLESPACE=expschema:newschema
That's fine, but I'm not sure how your example addresses the lack of
compression (without optional license) nor the ability for impdp to append
on insert when using a network link.
> Instead of suing INSERT APPEND, try use CTAS.
Why is that?
Your suggestion prompted me to test the INSERT vs CTAS. Both are able to
insert LOBs over DB Links and both perform similarly, although the CTAS
required very slightly more IO, presumably for the metadata to create the
table itself.
> If you can use transportable tablespace, then that is the way to go.
> Unfortunately, it does not work for us because it fails validation.
Another post asked about that, so I replied there instead.
Thanks for the feedback!
Rich
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx On Behalf Of Rich Jesse
> Subject: "Best" way to copy schema data to another DB
[snip]
> So I should use expdp/impdp, right? Not without the expensive Advanced
> Compression option I can't. My 200GB export compresses to 9GB with gzip
> and we don't have 300GB of disk (planning for growth) to "waste". I've
> also tried using the NETWORK_LINK option of impdp, but it refuses to use
> APPEND.
>
> I was hoping to work around this by scripting a set of INSERT
> /*+APPEND*/ SELECT statements using the same db link I created for the
> impdp test above.
> The problem with that approach is the occasional LOB column.
[snip]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
